Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Auto generate serial number in excel based on Column [Solved]

Report
Posts
22
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
January 18, 2021
 -
Posts
2696
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 28, 2021
 -
Dear All,

I would like to auto update Serial Number based on column D in excel using excel formula.

like:


Thanks
Raj
Related:

2 replies

Answer 1 / 2
Posts
2696
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 28, 2021
 457
Hi Raj,
  • (Manual run code) Yeah, it wasn't suppossed to run automatically. Place it in a standard module (Hit Alt+F11, goto top menu, Insert, Module, paste code in big white field). Hit Alt+F8 to open available macro's and double click RunMe, to see result.
  • (Auto run code) To run automatically, paste the following code in the big white field after right-clicking the sheets tab and clicking View Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("D:D")) Is Nothing Then Exit Sub
Dim x, lrow As Integer
lrow = Range("D1").End(xlDown).Row
Range("A2:A" & lrow).ClearContents
x = 1
For Each cell In Range("D1:D" & lrow - 1)
    If cell.Value <> cell.Offset(1, 0).Value Then
        cell.Offset(1, -3).Value = x
        x = x + 1
    End If
Next cell
End Sub

Then make a change in column D to see result.
  • (Formula reverse result) I tried to use standard functions, but all I could get was reverse result, so a 1 at the bottom and then increment to the top. Formula in cell A2: =IF(D1<>D2,COUNT(A3:A11)+1,"") and drag it down as far as needed.
  • (Custom function) If you really want to use a formula, I created a custom one for you. Place the code below in a standard module (just like mentioned "Manual run code" option):

Function SNo(dCell As Range)
If dCell.Value <> dCell.Offset(-1, 0).Value Then
    If dCell.Offset(-1, -3).Value = vbNullString Then
        SNo = Application.WorksheetFunction.Count(Range("A2:A" & dCell.Row - 1)) + 1
    ElseIf Application.WorksheetFunction.IsText(dCell.Offset(-1, -3).Value) = True Then
        SNo = 1
    ElseIf Application.WorksheetFunction.IsNumber(dCell.Offset(-1, -3).Value) = True Then
        SNo = dCell.Offset(-1, -3).Value + 1
    End If
Else
    SNo = vbNullString
End If
End Function

Then type in cell A2:
=SNo(D2)
and drag it down as far as needed.

Now there MUST be something to your liking :).

Best regards,
Trowa
Comment
1
Thank you
Thank you

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 2942 users have said thank you to us this month

Answer 2 / 2
Posts
2696
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 28, 2021
 457
Hey Raj,

Here you go: 
Sub RunMe()
Dim x As Integer
x = 1
For Each cell In Range("D1:D" & Range("D1").End(xlDown).Row - 1)
    If cell.Value <> cell.Offset(1, 0).Value Then
        cell.Offset(1, -3).Value = x
        x = x + 1
    End If
Next cell
End Sub


Best regards,
Trowa
Report
Posts
22
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
January 18, 2021
Hi Trowa,

Sorry! the code is not working.. (No action perform the code)

I would like to auto update the S.no after paste the value of Column D.

If the condition execute using excel formula is perfect!

Regards,
Raj
Report
Posts
22
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
January 18, 2021
 >
Posts
22
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
January 18, 2021
Hi Trowa,

I have tried as below formula. please correct me.



Thanks,
Raj
Reply Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!