Sequential Numbers based on like Values of another column 1

[ryplew]

Workbook is sent over by the customer so that we can enter their orders into our system. When they send over the file with multiple orders/items, I need to be able to add a line number to each row. Line numbers are sequential based on the PO Number where a new PO number would restart the sequence.

Basic structure (unnecessary data omitted) and expected result in Column B:

Column A | Column B
PO NUMBER | LINE NUMBER
ABC123 | 1
ABC123 | 2
DEF456 | 1
DEF456 | 2
DEF456 | 3
GHI789 | 1

Any thoughts on how to do this would be appreciated. Thank you!

 

[SkipVought]

Hi,

If you were to make a Formula on the sheet...
[tt]
B2: =IF(A2=A1,B1+1,1)
[/tt]
...or in plain language beginning with the position for the first LINE NUMBER, if this row’s PO NUMBER is identical to the previous row’s PO NUMBER then add 1 to the previous row’s LINE NUMBER, otherwise, make the LINE NUMBER 1.

So in VBA...

Dim r as Range

For Each r In Range([A2], [A2].End(xlDown))
    If r.Value = r.Offset(-1).Value Then
       r.Offset(0, 1).Value = r.Offset(-1, 1).Value + 1
    Else
       r.Offset(0, 1).Value = 1
    End If
Next

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ryplew]

Skip,

As always, you've given an excellent solution with a great explanation.

Thank you, sir! It works like a charm!

 

[SkipVought]

Here's another spreadsheet solution...
[tt]
B2: =SUMPRODUCT(--($A$1:A2=A2))
[/tt]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]