How to get nth distict value in a column
 

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

How to get nth distict value in a column
 

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"


"Si" wrote:

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

How to get nth distict value in a column
 

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)

"Si" wrote in message
oups.com...
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

How to get nth distict value in a column
 

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

How to get nth distict value in a column
 

Beautiful! I didn't supposed it could be done so easily. Well done
Bob! topola

How to get nth distict value in a column
 

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)

"Si" wrote in message
oups.com...
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

How to get nth distict value in a column
 

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)

How to get nth distict value in a column
 

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)))
....

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.