filter list of text for unique entries using formulea

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!

In article <[email protected]>,
The Nephalim <The [email protected]> wrote:

> 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.

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!

In article <[email protected]>,
The Nephalim <The [email protected]> wrote:

> 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.

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!

In article <[email protected]>,
The Nephalim <The [email protected]> wrote:

> 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.

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.

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!

In article <[email protected]>,
The Nephalim <The [email protected]> wrote:

> 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.