advanced filtering

[jim.davis]

Please accept my apology for not clearly explaining my
needs - i have a column with a list of 10 names, 7 are
the same (smith,smith,jones,jones, etc) 3 names are
different (hunter,hopper,bush) how do i get hunter,
hopper and bush to appear in a seperate column? thanks
again

 

[Jason Morin]

If your names are in A1:A10, select B1:B10, enter this
formula, and press ctrl + shift + enter:

=INDEX($A$1:$A$10,LARGE(IF(INDEX($A$1:$A$10,MAX(IF(MAX
(COUNTIF($A$1:$A$10,$A$1:$A$10))=COUNTIF
($A$1:$A$10,$A$1:$A$10),ROW($A$1:$A$10))))<>$A$1:$A$10,ROW
($A$1:$A$10)),ROW(INDIRECT("1:"&COUNTA(A1:A10)))))

Error values at the end mean there are no more unique
values.

HTH
Jason
Atlanta, GA

 

[Debra Dalgleish]

As answered at your previous post:

In the criteria area, leave the heading cell blank
In the cell below, enter a formula that refers to the column that
contains the names, and the first data cell in that column, e.g.:
=COUNTIF(A:A,A2)=1

When you run the Advanced Filter, select the column of names as the List
Select the blank heading cell, and the cell with the formula, as the
criteria range.

 

[Harlan Grove]

Please accept my apology for not clearly explaining my
needs - i have a column with a list of 10 names, 7 are
the same (smith,smith,jones,jones, etc) 3 names are
different (hunter,hopper,bush) how do i get hunter,
hopper and bush to appear in a seperate column? thanks
again

If your names were in A1:A10 and the resulting names should appear in col B with
the first one in B1, then try these array formulas.

B1:
=INDEX($A$1:$A$10,MATCH(1,COUNTIF($A$1:$A$10,$A$1:$A$10),0))

B2:
=INDEX($A$1:$A$10,MATCH(1,COUNTIF($A$1:$A$10,$A$1:$A$10)
*(COUNTIF($B$1:$B1,$A$1:$A$10)=0),0))

Select B2 and fill down until the formua returns #N/A.