# Serial number

### Question

• Hi Friends,

I need a clarification in excel 2007. I need your suggestion for the below.

I have the data in excel , column A contains customer account no and column B contains payment date. so each customer had multiple payments, i need to know which is the first payment and need payment date in ascending order, hope we can sort this one and find the first and last payment . now i need the serial number for those payment in column C. for eg. in column A account no 12345 had 13 payments i need serial number for all payments upto 13. after that for second customer it should start with again 1st payment like this , is it possible? please advice .

Regards,

Jay

Wednesday, July 10, 2013 9:40 PM

• Hello,

If you are asking about a macro to do those things automatically, you can refer to this snippet:

```Sub SortAndSerial()

Dim LastR As Integer
LastR = Cells(Rows.Count, "A").End(xlUp).Row

For Each Cell In Range(Cells(1, "A"), Cells(LastR, "A"))

Dim Top As Variant
Dim Bottom As Variant
Set Top = Cell.Offset(0, 1)

If Cell.Row = LastR Then
Set Top = Cell.Offset(0, 1)
Set Bottom = Cell.Offset(0, 1).End(xlDown)
Range(Top, Bottom).Sort Key1:=Top, Order1:=xlAscending
Set Top = Top.Offset(0, 1)
Set Bottom = Bottom.Offset(0, 1)
Top.Value = 1
Top.AutoFill Destination:=Range(Top, Bottom), Type:=xlFillSeries
ElseIf Not Cell.Value = "" Then
If Not Cell.Offset(1, 0).Value = "" Then
Top.Offset(0, 1).Value = 1
GoTo Continue
End If
Set Bottom = Cell.End(xlDown).Offset(-1, 1)
Range(Top, Bottom).Sort Key1:=Top, Order1:=xlAscending
Set Top = Top.Offset(0, 1)
Set Bottom = Bottom.Offset(0, 1)
Top.Value = 1
Top.AutoFill Destination:=Range(Top, Bottom), Type:=xlFillSeries
End If

Continue:
Next

End Sub```

Regards,

Damon Zheng
MSDN Community Support | Feedback to us
Develop and promote your apps in Windows Store
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Monday, July 15, 2013 8:14 PM
• Suppose your customer name starts at A1 and continues to A12.Then enter the below formula in C1 and paste till C12.

=COUNTIF(\$A\$1:A1,A1)

Best Regards,
---------------------------------------------------------------------------------------------
Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

Tuesday, July 16, 2013 6:35 AM

### All replies

• Hello,

If you are asking about a macro to do those things automatically, you can refer to this snippet:

```Sub SortAndSerial()

Dim LastR As Integer
LastR = Cells(Rows.Count, "A").End(xlUp).Row

For Each Cell In Range(Cells(1, "A"), Cells(LastR, "A"))

Dim Top As Variant
Dim Bottom As Variant
Set Top = Cell.Offset(0, 1)

If Cell.Row = LastR Then
Set Top = Cell.Offset(0, 1)
Set Bottom = Cell.Offset(0, 1).End(xlDown)
Range(Top, Bottom).Sort Key1:=Top, Order1:=xlAscending
Set Top = Top.Offset(0, 1)
Set Bottom = Bottom.Offset(0, 1)
Top.Value = 1
Top.AutoFill Destination:=Range(Top, Bottom), Type:=xlFillSeries
ElseIf Not Cell.Value = "" Then
If Not Cell.Offset(1, 0).Value = "" Then
Top.Offset(0, 1).Value = 1
GoTo Continue
End If
Set Bottom = Cell.End(xlDown).Offset(-1, 1)
Range(Top, Bottom).Sort Key1:=Top, Order1:=xlAscending
Set Top = Top.Offset(0, 1)
Set Bottom = Bottom.Offset(0, 1)
Top.Value = 1
Top.AutoFill Destination:=Range(Top, Bottom), Type:=xlFillSeries
End If

Continue:
Next

End Sub```

Regards,

Damon Zheng
MSDN Community Support | Feedback to us
Develop and promote your apps in Windows Store
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Monday, July 15, 2013 8:14 PM
• Suppose your customer name starts at A1 and continues to A12.Then enter the below formula in C1 and paste till C12.

=COUNTIF(\$A\$1:A1,A1)

Best Regards,
---------------------------------------------------------------------------------------------
Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

Tuesday, July 16, 2013 6:35 AM
• Suppose your customer name starts at A1 and continues to A12.Then enter the below formula in C1 and paste till C12.

=COUNTIF(\$A\$1:A1,A1)

Best Regards,
---------------------------------------------------------------------------------------------
Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

There might be blank cells between two customer name because each would have multiple payments
Tuesday, July 16, 2013 7:50 AM
• Hi Javed,

This is great, thanks so much for this one.

Regards,

Jay

Monday, July 22, 2013 12:09 PM