Auto generate serial number in excel based on Column

Solved/Closed
Raj_1562 Posts 29 Registration date Wednesday August 26, 2020 Status Member Last seen August 29, 2022 - Updated on Sep 14, 2020 at 09:38 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Sep 15, 2020 at 12:20 PM
Dear All,

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

like:


Thanks
Raj
Related:

2 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Sep 15, 2020 at 12:20 PM
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