How to get nth distict value in a column

S

Si

Hi,

Is there anyone clever enough to know how to work this out?
I've got a column with a list of data. In a summary I'd like to be
able to get a distinct list from the column.

So from the following source data:

Column
-----------
IB3\SJ16
IB3\SJ16
IB3\SJ16
SJ17
SJ17
IB3
IB3\SJ16
Verdi
SJ17
Tax Certs
Verdi
Verdi

I'd like to be able to get

Summary Column
-------------
IB3\SJ16
SJ17
IB3
Verdi
Tax Certs

I've tried playing with the various LOOKUP functions but this has got
me stumped. Can anyone point in the direction on how to get this
working.

Many thanks
Si
 
D

Duke Carey

Use Data->Filter->Advanced Filter

Select "Copy to another location" and indicate the range to copy to in the
bottom edit box, then be sure to check "Unique records only"
 
B

Bob Phillips

In B1: =A1
In B2: =IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""),0)),"",
INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH(0,COUNTIF(B$1:B1,$A$1:$A$2
0&""),0)))

which is an array formula, so commit with Ctrl-Shift-Enter

Copy B2 down.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
S

Si

Thanks Bob, this is exactly what I was looking for and it works a
treat!

Thanks for your time Duke, but I was hoping for a function.

Cheers
Si
 
B

Bob Phillips

The good thing about this is that if you change a value in the source data,
the target data is automatically updated.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
H

Herbert Seidenberg

This might appeal to beginners.
Highlight all duplicate cells in your list with conditional formatting,
then delete those cells.
Use R1C1, then switch back to A1.
Insert > Name > Create or Define (say ListA)
Conditional Formatting > Formula Is
=MATCH(RC,ListA,0)<>(ROW()-ROW(ListA)+1)
 
H

Harlan Grove

Bob Phillips wrote...
In B1: =A1
In B2: =IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""),0)),"",
INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),
MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""),0)))
Click to expand...
....

Compact form

B2:
=IF(SUM(COUNTIF(B$1:B1,A$1:A$20))<COUNTA(A$1:A$20),
INDEX(A$1:A$20,MATCH(0,COUNTIF(B$1:B1,A$1:A$20),0)),"")

As for the general question of the n_th distinct value in a list (LST),

=INDEX(LST,SMALL(IF(MATCH(LST,LST,0)=ROW(LST)-ROW(INDEX(LST,1,1))+1,
MATCH(LST,LST,0)),n))

also an array formula.
 
Top