Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Sequential Numbers based on like Values of another column 1

Status
Not open for further replies.

ryplew

Technical User
Jun 3, 2015
18
US
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!
 
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...
Code:
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]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Skip,

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

Thank you, sir! It works like a charm!
 
Here's another spreadsheet solution...
[tt]
B2: =SUMPRODUCT(--($A$1:A2=A2))
[/tt]


Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top