filter list of text for unique entries using formulea

T

The Nephalim

I would like to create a list of unique entries existing in a range of text
and blank cells that is allways current (eg: using formulea rather than code
or filetering). Duplicate cells might not be adjacent, similarly the blanks
might not.
 
D

Domenic

Here's an approach I learned from Aladin Akyurek...

Assuming that Column A contains your data, starting at A2, try the
following...

B1: enter a 0 (zero)

B2, copied down:

=IF((A2<>"")*(ISNA(MATCH(A2,$A$1:A1,0))),LOOKUP(9.99999999999999E+307,$B$
1:B1)+1,"")

C1:

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

D2, copied down:

=IF(ROW()-ROW($D$2)+1<=$C$1,MATCH(ROW()-ROW($D$2)+1,$B:$B,0),"")

E2, copied down:

=IF(N($D2),INDEX(A:A,$D2),"")

Hope this helps!
 
Top