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

[Brian]

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

 

[Domenic]

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.99999999999999E+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(ROWS(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]

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.99999999999999E+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(ROWS(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))),"")

 

[Ashish Mathur]

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:B5>0)*(B2:B5>MAX($C$1:C1)),B2:B5))>0,MIN(IF((B2:B5>0)*(B2:B5>MAX($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

 

[Ashish Mathur]

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:B5>0)*(B2:B5>MAX($C$1:C1)),B2:B5))>0,MIN(IF((B2:B5>0)*(B2:B5>MAX($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]

Thanks Domenic, works like a charm.

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.99999999999999E+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(ROWS(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))),"")

 

[Brian]

Thanks Domenic, works like a charm.

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.99999999999999E+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(ROWS(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))),"")