×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Sequential Numbers based on like Values of another column

## Sequential Numbers based on like Values of another column

(OP)
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!

### RE: Sequential Numbers based on like Values of another column

Hi,

If you were to make a Formula on the sheet...

B2: =IF(A2=A1,B1+1,1)

...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,

Just traded in my OLD subtlety...
for a NUance!

### RE: Sequential Numbers based on like Values of another column

(OP)
Skip,

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

Thank you, sir! It works like a charm!

### RE: Sequential Numbers based on like Values of another column

B2: =SUMPRODUCT(--($A$1:A2=A2))

Skip,

Just traded in my OLD subtlety...
for a NUance!

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!