Auto generate serial number in excel based on input value

Solved/Closed

Excel

Raj - Updated on Aug 26, 2020 at 11:26 AM
Raj - Sep 7, 2020 at 11:33 AM

Dear Expert,

I want to auto generate the S.No in column A5 based on input value. I have to enter the No. of record in column C3(Ex: 3).The serial Number will copy auto column A5 (1),A6(2) and A6(3).

The No.Of record is user Input value.

Can you please on this via Excel formula or Vba?

Thanks
Raj

Auto serial number in excel based on another column
Auto generate serial number in excel based on Column ✓ - Excel Forum
Grand theft auto 1 download - Download - Action and adventure
How to change account based in instagram - Instagram Forum
Number to words in excel without vba - Guide
Nvidia drivers auto detect - Guide

6 responses

Answer 1 / 6

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 549
Aug 31, 2020 at 11:56 AM

Hi Raj,

This confuses me: 'I would like to display the 3 combination in one'.

I interpreted this as you want the solution to work for all 3 scenarios.

The code below will fire once you make a change in cell G3:
- Column A, starting at row 6 will be cleared.
- Column A, starting at row 6 will be numbered to the amount entered in cell G3.
- Spacing between the numbers will be determined by how many cells in range C3:E3 contain data.

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim mStep, NoR, x, y As Integer

If Intersect(Target, Range("G3")) Is Nothing Then Exit Sub

mStep = WorksheetFunction.CountA(Range("C3:E3"))
NoR = Target.Value
x = 6

Range("A6:A" & Rows.Count).ClearContents

Do
    y = y + 1
    Cells(x, "A").Value = y
    x = x + mStep
    NoR = NoR - 1
Loop Until NoR = 0

End Sub

Using the code is done by right-clicking your sheets tab and select "View code". Paste the code in the big white field that pops up.

Best regards,
Trowa

Raj
Updated on Aug 31, 2020 at 12:24 PM

Hi Trowa,

There is no words to say congrats in this world to you!. because you are amazing! Keep Rocking!
Many congrats!

Note: This is exactly what I was expecting!

Regards,
Raj

Answer 2 / 6

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 549
Sep 1, 2020 at 11:16 AM

Awesome! Glad I could help.

Raj
Sep 3, 2020 at 05:55 AM

Hi TrowaD,

I have a concern!

When I enter the No.of Record(G3). It takes a while to update the value in the No.of Record(G3).

Kindly help me to reduce the time.because i'm waiting for that time to update the next cell value in excel sheet.

Regards,
Raj

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 549 > Raj
Sep 3, 2020 at 11:48 AM

Hi Raj,

I entered a value of 32000 in G3 and the code takes about a second to run. I don't think you would consider that slow, right? So there must be something else going on in your workbook that slows the process down. I can't tell you what that is without having a look.

Consider uploading your workbook to a free filesharing site and then post back the download link here. Always be carefull with sensitive data.

Best regards,
Trowa

Raj > TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023
Sep 3, 2020 at 12:12 PM

Thanks Trowa for your reply.

I'll check in empty file the logic and let you know you.

Thanks ????.

Regards,
Raj

Raj > TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023
Sep 4, 2020 at 10:15 AM

Hi Trowa,

Hope you are doing good ????!

The issue in my system.after clear the temp and cookies from the system.the value getting update quickly.

Sorry for the inconveniences!

Regards,
Raj

Answer 5 / 6

Raj_1562 Posts 31 Registration date Wednesday August 26, 2020 Status Member Last seen August 29, 2022
Updated on Aug 27, 2020 at 12:20 PM

Hi Trowa,

Thank you so much for your reply on this!

Sure! I have attached the screenshot of the request.I would like to display the 3 combination in one ( I differencient the 3 combination for your understanding).

Hope it will help you to understand my expectation.

Thanks in advance!

Ragards,
Raj

Raj_1562 Posts 31 Registration date Wednesday August 26, 2020 Status Member Last seen August 29, 2022
Updated on Sep 4, 2020 at 10:29 PM

Hi Trowa,

I need one more help from you!

I want to add one more condition from the existing.

attached the screenshot for your understanding (I would like to display the 3 combination in one)

I would like to copy the teams name in teams column.

Thanks in advance!

Regards,
Raj

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 549 > Raj_1562 Posts 31 Registration date Wednesday August 26, 2020 Status Member Last seen August 29, 2022
Sep 7, 2020 at 11:27 AM

Good that you created a separate thread for that request:
https://ccm.net/forum/affich-1119616-how-to-copy-the-column-value-into-rows

Raj > TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023
Sep 7, 2020 at 11:33 AM

Yes TrowaD,

Because the old thread is solved. So, I have created a new. Can you please help me on that.because you know very well ????.

Regards,
Raj

Similar discussions

Excel - To generate unique ID

How to generate a STATIC unique identifier for each Excel row

Auto generate serial number in excel based on Column

Autogenerate alphanumeric ID numbers in Excel?

Excel if range of cell contain a text put a cell value in

If a cell contains any text then add a value

Similar discussions

Subscribe To Our Newsletter!