Method to number unique values

English
, , ,
#1

Hello World :slight_smile:

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 :slight_smile:

#2

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

#3

thats stupid, because there about 750 different texts.

#4

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

#5

Works !! Thx Karolus !!

#6

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.

#7

I must add that this doesn’t scale well; I recomment @karolus’ answer instead.

#8

Karolus’ solution worked smoothly. Thx guys !!! :slight_smile:

#9

If the answer solves your question please tick the :heavy_check_mark:.

#10

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

#11

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.