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 !!

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

thats stupid, because there about 750 different texts.

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

Works !! Thx Karolus !!

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.

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