Formula to Number in Order Each Sub Set Out of a Large Data Set of Duplicate Values

[Sarkman22]

Hello all- thanks for reading and any help....

I have a large data set of around 75,000 numbers. There are around 3,500 unique numbers, with varying quantities of duplicates. My data is sorted in ascending order so all duplicates are in order. What I would like to do is write a formula to have Excel tell me which duplicate value the number is.

E.g. (also shown below) I have the number 111 and it is listed 5 times. I want the first 111 to say "1",the second 111 to say "2", and so forth. But once I get to the next unique value, 222, I want the numbering to start over with 1.

Does anyone know how to automate this using formulas? I have put a small example of what I want below to help clarify.

NOTE: It is very important that the numbers (column B) stay in this exact order.

Thanks and I hope this explanation is clear enough!

Andy

COLUMN A COLUMN B
1 111
2 111
3 111
4 111
5 111
1 222
2 222
3 222
1 333
1 444
2 444
3 444
4 444
1 555
2 555

 

[bbott]

Welcome to the board.

Place a 1 in cell A1. Then place this formula in A2 and copy down.

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

 

[Sarkman22]

Wow, thank you so much for the quick reply bbott! This worked beautifully.

I knew there was a relatively simple solution, just couldn't think on a Friday

 

[bbott]

Wow, thank you so much for the quick reply bbott! This worked beautifully.

I knew there was a relatively simple solution, just couldn't think on a Friday

You're welcome.