Excel formula - list numbers for unique entries

[jayherring86]

Hi everyone, I think my question will have an easy answer. I have a list of items in colA, to row 23000. In order to run analysis I need to find a way to assign a number to each item in the list, but duplicate items need to show the same ID number.

The list is currently unsorted, and contains text, numbers, and mixed cells formatted as "general".

Thanks in advance, i'm happy to provide extra detail if needed.

[AlKey]

You can try this formula but it will work on a sorted column only and without blanks.

In B1 and copy down

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

Row\Col	A	B
1	Item1	1
2	unsorted	2
3	Item2	3
4	Item2	3
5	contains text	4
6	Item3	5
7	Item3	5
8	numbers	6
9	numbers	6
10	Item5	7

[jhren]

You can try this formula but it will work on a sorted column only and without blanks.

Didn't try your formula, but you said column must be sorted yet your demo table column A is not...???

[AlKey]

@jhren

I meant sorted in the sense that duplicates must be together but not necessarily in alphabetical order although it would be fine too.

[jayherring86]

Ah, sumproduct. I knew there was an easy way but don't use sumproduct very often so it didn't even cross my mind.

I can sort the range no problem so this solution will work for me (i'm writing this while it is untested, but just looking at the formula and your example i know it would be perfect).

I see stars in your future Mr. AlKey

** Note to all future people who come to this thread from Google (or whatever future-magic brings you here) - The solution in this thread will work for what you need, but the Sumproduct function is slow when you use it like this on a large range. Mine is c.23000 rows, so will take a while to calculate on my puny quad core i5 processor. This is fine for my own purpose because once it has run I will paste values and never need it again but obviously, leaving the formulas in and changing the data will result in long waiting times - unless of course you live a future where the quantum computer is a real thing, in which case disregard this and resume your life :D **

[Tony Valko]

This should calculate much faster...

Data Range

	A	B
1	Header	Header
2	Item1	1
3	unsorted	2
4	Item2	3
5	Item2	3
6	contains text	4
7	Item3	5
8	Item3	5
9	numbers	6
10	numbers	6
11	Item5	7

Enter 1 in B2

Enter this formula in B3 and copy down as needed:

=IF(A3=A2,B2,B2+1)

[Tony Valko]

P.S.

On 23k rows of data this is how many cells are being evaluated using the SUMPRODUCT/COUNTIF formula:

=SUMPRODUCT(ROW(A1:A23000))