How to assign a unique number per group of entries in a column?

Question

I have a list of part numbers in Column A and a location in Column S. I need to assign a value of 1 for all part numbers that are the same (A3-A16), then change to a value of 2 when the part number changes (A18-A22), and so on a so forth. Basically, all like part numbers will have a unique value starting at one and ending when the data ends. Help?

Solution

If column A is sorted, then put 1 in X1 and =IF(A2=A1;X1;X1+1) (change all the X's to the column where you want the part number). Copy-paste X2 down as far as needed.

As an (bit more complex) alternative, you can follow this suggestion by Microsoft and put this formula in X1 and copy-paste down: =SUM(IF(FREQUENCY($A$1:A1,$A$1:A1)>0,1)). Using this solution, the data in column A doesn't need to be sorted.

Comments

[Xavierjazz]

Are these kind of questions allowed in the FAQ?

[user360300]

It was a last minute requirement and I needed to get the job done.

[user360300]

Thank you agtoever, super simple and I totally have done that before, but couldn't get it out of my brain.