Assign incremental numbers to matching Invoice numbers

[berger01]

I have a list of Invoice numbers (6000 +) that have many duplicates. I need a formla in column B to assign incremental values (numbers) to all of the same invoice numbers in column A. So if there are 5 of the same invoices#'s in column A, those 5 will all have the same value assigned. Incrementing the numbers assigned will allow me to have a runnng count of invoices

A B
Invoice# Expected Result

802951-1330 1
802951-0640 2
802951-1330 1
802951-0640 2
802951-1330 1
802951-0640 2
802951-1330 1
802976-3040 3
802976-3040 3
802976-3040 3
802976-3040 3


As you can see, some of the same invoices may be located in differeant rows throughout the list. I'm thinking of maybe a VLookup + Match or something like that, but not real sure how to start.


Thanks in advance.

[benishiryo]

hi berger01. assuming data starts in A2, try this array formula:
=IF(COUNTIF(A$2:A2,A2)=1,SUM(SIGN(FREQUENCY(MATCH(A$2:A2,A$2:A2,0),ROW(A$2:A2)-ROW($A$2)+1))),VLOOKUP(A2,A$1:B1,2,0))

...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[berger01]

Pure Genius! Thanks so much