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

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

Report
Posts
21
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
October 13, 2020
 -
Posts
2665
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 12, 2020
 -
Dear All,

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

like:


Thanks
Raj
See more

2 replies

Answer 1 / 2
Posts
2665
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 12, 2020
 445
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
Thank you
1
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
2665
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 12, 2020
 445
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
21
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
October 13, 2020
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
21
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
October 13, 2020
 >
Posts
21
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
October 13, 2020
Hi Trowa,

I have tried as below formula. please correct me.



Thanks,
Raj
Reply Ask a question