Duplicate Counting Nice Easy question!!!!!

Hi Guys and Gals!!

I have a list of products in an excel spreadsheet and I need to give duplicates titles a matching code which for ease I'm starting at 1.

For example: Column A has the title and column B has the number I want to generate using some code. Counting upwards from 1.


Manchester United FC Stripe Ring 1
Manchester United FC Stripe Ring 1
Manchester United FC Stripe Ring 1
Manchester United FC Super Titanium Ring 2
Manchester United FC Super Titanium Ring 2
Manchester United FC Super Titanium Ring 2
Manchester United FC Velcro Boot Crib 3
Manchester United FC Velcro Boot Crib 3 etc. etc.

There must be a very simple bit of code (that is beyond me!!) that will do column B for me as currently I'm doing manually and it taking me forever and therefore easier to make a mistake!!!! I assume I should uses an IF statement that tells the Cell in column B to add 1 to the value of the previous cell if it is not a duplicate and to remain the same if it is.

Can you help please with writing of some simple code to do this!!

Thank you

Stephen

Try this one

=SUMPRODUCT(1/COUNTIF(A$1:A1,A$1:A1))

Row\Col	A	B
1	Manchester United FC Stripe Ring	1
2	Manchester United FC Stripe Ring	1
3	Manchester United FC Stripe Ring	1
4	Manchester United FC Super Titanium Ring	2
5	Manchester United FC Super Titanium Ring	2
6	Manchester United FC Super Titanium Ring	2
7	Manchester United FC Velcro Boot Crib	3
8	Manchester United FC Velcro Boot Crib	3

Hi,

It's not clear what's in column A and what's in Column B but you could try in B1 copied down.

Formula:

Please Login or Register  to view this content.

which assumes MANCHESTER UNITED FC is in A1 and Stripe ring,..etc in B1

You're welcome and thank you for the feedback!

Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools -> Mark thread as Solved).