Ask Your Question
1

Method to number unique values

asked 2017-03-16 16:09:56 +0100

tentacle gravatar image

updated 2017-03-16 16:10:44 +0100

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 flag offensive close merge delete

4 Answers

Sort by » oldest newest most voted
0

answered 2017-03-16 16:54:18 +0100

karolus gravatar image

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

edit flag offensive delete link more

Comments

Works !! Thx Karolus !!

tentacle gravatar imagetentacle ( 2017-03-17 19:29:22 +0100 )edit
0

answered 2017-03-17 19:28:46 +0100

tentacle gravatar image

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

edit flag offensive delete link more

Comments

If the answer solves your question please tick the ✔.

m.a.riosv gravatar imagem.a.riosv ( 2017-03-17 22:53:21 +0100 )edit
0

answered 2017-03-17 06:21:30 +0100

updated 2017-03-17 06:59:20 +0100

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.

edit flag offensive delete link more

Comments

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

Mike Kaganski gravatar imageMike Kaganski ( 2017-03-17 09:30:24 +0100 )edit
-1

answered 2017-03-16 16:18:12 +0100

Zeca gravatar image

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

edit flag offensive delete link more

Comments

thats stupid, because there about 750 different texts.

karolus gravatar imagekarolus ( 2017-03-16 16:43:15 +0100 )edit
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2017-03-16 16:09:56 +0100

Seen: 591 times

Last updated: Mar 17 '17