Filter a list using a formula.

[Charno]

i have a list of names in column A15:A1014. The names will appear more than
once in the list. I want to filter this list into another column AQ15:AQ1014
so that only unique enteries are shown, but i want this as a formula rather
than using the advance filter option so that as the data changes in the first
column it is automatically updated in the second column.

Chris

 

[Bob Phillips]

AQ15: =A15
AQ16: =IF(ISERROR(MATCH(0,COUNTIF(AQ$15:AQ15,$A$15:$A$1014&""),0)),"",
INDEX(IF(ISBLANK($A$15:$A$1014),"",$A$15:$A$1014),MATCH(0,COUNTIF(AQ$15:AQ15,$A$15:$A$1014&""),0)))

AQ16 is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Copy AQ16 down as far as you will ever need.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Charno]

That worked great thank you !!

Cheers

Charno

AQ15: =A15
AQ16: =IF(ISERROR(MATCH(0,COUNTIF(AQ$15:AQ15,$A$15:$A$1014&""),0)),"",
INDEX(IF(ISBLANK($A$15:$A$1014),"",$A$15:$A$1014),MATCH(0,COUNTIF(AQ$15:AQ15,$A$15:$A$1014&""),0)))

AQ16 is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Copy AQ16 down as far as you will ever need.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)