software

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
SiUse Data-gt;Filter-gt;Advanced Filter

Select quot;Copy to another locationquot; and indicate the range to copy to in the
bottom edit box, then be sure to check quot;Unique records onlyquot;quot;Siquot; wrote:

gt; Hi,
gt;
gt; Is there anyone clever enough to know how to work this out?
gt; I've got a column with a list of data. In a summary I'd like to be
gt; able to get a distinct list from the column.
gt;
gt; So from the following source data:
gt;
gt; Column
gt; -----------
gt; IB3\SJ16
gt; IB3\SJ16
gt; IB3\SJ16
gt; SJ17
gt; SJ17
gt; IB3
gt; IB3\SJ16
gt; Verdi
gt; SJ17
gt; Tax Certs
gt; Verdi
gt; Verdi
gt;
gt; I'd like to be able to get
gt;
gt; Summary Column
gt; -------------
gt; IB3\SJ16
gt; SJ17
gt; IB3
gt; Verdi
gt; Tax Certs
gt;
gt; I've tried playing with the various LOOKUP functions but this has got
gt; me stumped. Can anyone point in the direction on how to get this
gt; working.
gt;
gt; Many thanks
gt; Si
gt;
gt;

In B1: =A1
In B2: =IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20amp;quot;quot;), 0)),quot;quot;,
INDEX(IF(ISBLANK($A$1:$A$20),quot;quot;,$A$1:$A$20),MATCH( 0,COUNTIF(B$1:B1,$A$1:$A$2
0amp;quot;quot;),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)

quot;Siquot; gt; wrote in message oups.com...
gt; Hi,
gt;
gt; Is there anyone clever enough to know how to work this out?
gt; I've got a column with a list of data. In a summary I'd like to be
gt; able to get a distinct list from the column.
gt;
gt; So from the following source data:
gt;
gt; Column
gt; -----------
gt; IB3\SJ16
gt; IB3\SJ16
gt; IB3\SJ16
gt; SJ17
gt; SJ17
gt; IB3
gt; IB3\SJ16
gt; Verdi
gt; SJ17
gt; Tax Certs
gt; Verdi
gt; Verdi
gt;
gt; I'd like to be able to get
gt;
gt; Summary Column
gt; -------------
gt; IB3\SJ16
gt; SJ17
gt; IB3
gt; Verdi
gt; Tax Certs
gt;
gt; I've tried playing with the various LOOKUP functions but this has got
gt; me stumped. Can anyone point in the direction on how to get this
gt; working.
gt;
gt; Many thanks
gt; Si
gt;
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
SiBeautiful! I didn't supposed it could be done so easily. Well done
Bob! topolaThe 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)

quot;Siquot; gt; wrote in message oups.com...
gt; Thanks Bob, this is exactly what I was looking for and it works a
gt; treat!
gt;
gt; Thanks for your time Duke, but I was hoping for a function.
gt;
gt; Cheers
gt; Si
gt;
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 gt; Name gt; Create or Define (say ListA)
Conditional Formatting gt; Formula Is
=MATCH(RC,ListA,0)lt;gt;(ROW()-ROW(ListA) 1)Bob Phillips wrote...
gt;In B1: =A1
gt;In B2: =IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20amp;quot;quot;), 0)),quot;quot;,
gt;INDEX(IF(ISBLANK($A$1:$A$20),quot;quot;,$A$1:$A$20),
gt;MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20amp;quot;quot;),0)))
....

Compact form

B2:
=IF(SUM(COUNTIF(B$1:B1,A$1:A$20))lt;COUNTA(A$1:A$20) ,
INDEX(A$1:A$20,MATCH(0,COUNTIF(B$1:B1,A$1:A$20),0) ),quot;quot;)

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.