×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

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

Students Click Here

Jobs

Sequential Numbers based on like Values of another column

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

Here's another spreadsheet solution...

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close