Extract unique data from table

Question

Hi,

I have a table a1:c10 (data: text, numbers).

I want to extract the unique data in column D.

Ron Coderre's formula:

returns (in column D) the value that has the cell A1.

(i have Excel 2003)

Thank you all in advance.

Answer 1

The D2 formula is an array formula and must be committed by pressing CTRL+SHIFT+ENTER. (instead of just ENTER)

In my testing it listed each unique value in the A1:C10 range, when copied down Col_D.

Are you having an issue with that formula?

Answer 2

Came across your solution ... Thanks for sharing!

Answer 3

Ron Coderre's formula:

D1: =A1

D2: =INDEX($A$1:$C$10,INT(SMALL(IF(COUNTIF(D$1:D1,$A$1:$C$10)=0,ROW($A$1:$C$10)+(COLUMN($A$1:$C$10)*0.01)),1)),100*MOD(SMALL(IF(COUNTIF(D$1:D1,$A$1:$C$10)=0,ROW($A$1:$C$10)+(COLUMN($A$1:$C$10)*0.01)),1),1))

Hi Ron,

The above formula has problems in row insertion. I even tried to replace ROW() & Column() with Indirect() but get error.

Answer 4

Hi Ron,

I found this formula in a reply to Horatio J. Bilge

(question: List unique values in range).

I copy down as simple formula.

Now the array formula works perfectly.

Thank you.

Answer 5

Ron Coderre's formula:

D1: =A1

D2: =INDEX($A$1:$C$10,INT(SMALL(IF(COUNTIF(D$1:D1,$A$1:$C$10)=0,ROW($A$1:$C$10)+(COLUMN($A$1:$C$10)*0.01)),1)),100*MOD(SMALL(IF(COUNTIF(D$1:D1,$A$1:$C$10)=0,ROW($A$1:$C$10)+(COLUMN($A$1:$C$10)*0.01)),1),1))