Formula (not adv. filter) to list unique values from list

Howdy all,

I have a list of names which appear multiple times in a column with about
20,000 entries.

I want to create a list of the unique names on a new sheet, but I don't want
to use the advanced filter technique because I want to be able to refresh
this list when new data is entered.

Thanks for your time and attention!

Brian

In article ,
"Brian" wrote:

Howdy all,

I have a list of names which appear multiple times in a column with about
20,000 entries.

I want to create a list of the unique names on a new sheet, but I don't want
to use the advanced filter technique because I want to be able to refresh
this list when new data is entered.

Thanks for your time and attention!

Brian

Let's assume that A2:A100 contains the data. Here's two possibilities.
The first one uses a helper column and should be more efficient.

[Option 1]

B1: 0

(Enter a 0 in B1)

B2, copied down:

=IF(A2"",IF(ISNA(MATCH(A2,$A$1:A1,0)),LOOKUP(9.9 9999999999999E+307,$B$1
:B1)+1,""),"")

D2:

=LOOKUP(9.99999999999999E+307,B:B)

E2, copied down:

=IF(ROWS(E$2:E2)=$D$2,INDEX($A$2:$A$100,MATCH(ROW S(E$2:E2),$B$2:$B$100,0
)),"")

[Option 2]

C2, confirmed with CONTROL+SHIFT+ENTER:

=SUM(IF(FREQUENCY(IF(A2:A100"",MATCH("~"&A2:A100 ,A2:A100&"",0)),ROW(A2:
A100)-ROW(A2)+1),1))

D2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS(D$22)=$C$2,INDEX($A$2:$A$100,SMALL(IF( FREQUENCY(IF($A$2:$A$10
0"",MATCH("~"&$A$2:$A$100,$A$2:$A$100&"",0)),ROW ($A$2:$A$100)-ROW($A$2)
+1),ROW($A$2:$A$100)-ROW($A$2)+1),ROWS(D$22))),"")

--
Domenic
http://www.xl-central.com

Hi,

You can also try this. Assume your list is in A2:A5. In B2, enter
=IF(COUNTIF($A$2:$A2,A2)1,0,MAX($B$1:B1)+1) and copy down till B5. In cell
C2, array enter (Ctrl+Shift+Enter)
=IF(MIN(IF((B2:B50)*(B2:B5MAX($C$1:C1)),B2:B5)) 0,MIN(IF((B2:B50)*(B2:B5MAX($C$1:C1)),B2:B5)),"" )
and copy down.

Now in cell D2, enter
=IF(ISERROR(INDEX($A$2:$B$5,MATCH(C2,$B$2:$B$5,0), 1)),"",INDEX($A$2:$B$5,MATCH(C2,$B$2:$B$5,0),1))
and copy down

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Brian" wrote in message
...
Howdy all,

I have a list of names which appear multiple times in a column with about
20,000 entries.

I want to create a list of the unique names on a new sheet, but I don't
want to use the advanced filter technique because I want to be able to
refresh this list when new data is entered.

Thanks for your time and attention!

Brian

Thanks Domenic, works like a charm.


"Domenic" wrote in message
...
In article ,
"Brian" wrote:

Howdy all,

I have a list of names which appear multiple times in a column with about
20,000 entries.

I want to create a list of the unique names on a new sheet, but I don't
want
to use the advanced filter technique because I want to be able to refresh
this list when new data is entered.

Thanks for your time and attention!

Brian

Let's assume that A2:A100 contains the data. Here's two possibilities.
The first one uses a helper column and should be more efficient.

[Option 1]

B1: 0

(Enter a 0 in B1)

B2, copied down:

=IF(A2"",IF(ISNA(MATCH(A2,$A$1:A1,0)),LOOKUP(9.9 9999999999999E+307,$B$1
:B1)+1,""),"")

D2:

=LOOKUP(9.99999999999999E+307,B:B)

E2, copied down:

=IF(ROWS(E$2:E2)=$D$2,INDEX($A$2:$A$100,MATCH(ROW S(E$2:E2),$B$2:$B$100,0
)),"")

[Option 2]

C2, confirmed with CONTROL+SHIFT+ENTER:

=SUM(IF(FREQUENCY(IF(A2:A100"",MATCH("~"&A2:A100 ,A2:A100&"",0)),ROW(A2:
A100)-ROW(A2)+1),1))

D2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS(D$22)=$C$2,INDEX($A$2:$A$100,SMALL(IF( FREQUENCY(IF($A$2:$A$10
0"",MATCH("~"&$A$2:$A$100,$A$2:$A$100&"",0)),ROW ($A$2:$A$100)-ROW($A$2)
+1),ROW($A$2:$A$100)-ROW($A$2)+1),ROWS(D$22))),"")

--
Domenic
http://www.xl-central.com