# Method to number unique values

Hello World :)

I have a calc-sheet with some 900000 lines in it. In one of the columns the same text values keep showing up. There are about 750 different text values.

it looks like this:

1. AAA
2. AAA
3. BBB
4. AAA
5. AAA
6. AAA
7. CCC
8. BBB

So now I would like to add a number next to every unique value. The same number every time that is.

Ideally it would look like this:

1. AAA 1
2. AAA 1
3. BBB 2
4. AAA 1
5. AAA 1
6. AAA 1
7. CCC 3
8. BBB 2

How would I go about this ?? Do I really have to auto-filter, select one of the values and copy paste the correct number in the column next to it (repeated 750 times!!) or is there an easier way ??

All help greatly appreciated !! Thanks in advance :)

edit retag close merge delete

Sort by » oldest newest most voted

First you need a reference-range of 750 rows and 2 Columns:

→Data→Filter→Standardfilter →→[x]without Duplicates into [x]other …
in the Column right of filtered Output fill with Formula =ROW()

use right to your original Data :

=VLOOKUP( A1 ;reference_Range;2;0)

and filldown

more

Works !! Thx Karolus !!

( 2017-03-17 19:29:22 +0100 )edit

You may create a non-sequential, but still unique numbering, using this formula in your B column (given your example is in A1:A8; the formula is put into B1 and drag-copied to B2:B8):

=MATCH(A1;$A$1:$A$8;0)

This gives you:

AAA 1
AAA 1
BBB 3
AAA 1
AAA 1
AAA 1
CCC 7
BBB 3


Further, if you require sequential numbering, you may add two more columns: C and D, with formula in C1: =SUM(1/COUNTIF($B$1:$B$8;B1)), and in D1: =SUMIF($B$1:$B$8;"<="&B1;$C$1:$C$8).

This will give you:

AAA 1   0,2 1
AAA 1   0,2 1
BBB 3   0,5 2
AAA 1   0,2 1
AAA 1   0,2 1
AAA 1   0,2 1
CCC 7   1   3
BBB 3   0,5 2


The last column is the required numbering; columns B and C may be hidden.

more

( 2017-03-17 09:30:24 +0100 )edit

This is a function that helped me

=IF(COUNTIF(A$2:A2,A2)=1,MAX(B$1:B1)+1,VLOOKUP(A2,A\$1:B1,2,0))


*Note - the first row is the title of the columns

more

Karolus' solution worked smoothly. Thx guys !!! :)

more

( 2017-03-17 22:53:21 +0100 )edit

Hi!

I can think of solving it using nested IF statements. Suppose your letters are start in cell A1 and go down to cell 900000. The you just use this formula on column B to filter through the possible values:

=IF(A1="AAA",1,IF(A1="BBB",2,IF(A1="CCC",3)))

You can add more "DDD"s and "EEE"s if you want. Hope that helps!

Regards

more