Unique values formula

[agibsonsw]

Hello. Excel 2003.
I've managed to define the following array formula as a Named Range called Uniques:
=IF(ISNA(INDEX($A$2:$A$12,MATCH(0,IF(ISBLANK($A$2:$A$12),"",COUNTIF(E$1:E1,$A$2:$A$12)),0))),"",INDEX($A$2:$A$12,MATCH(0,IF(ISBLANK($A$2:$A$12),"",COUNTIF(E$1:E1,$A$2:$A$12)),0)))
If I enter =Uniques in E2 and drag it down it produces a list of unique values from column A (ignoring blank cells) and not showing #NA.
But if I try to use =Uniques as the input range for a (Forms) combo box it fails.
Does anyone know if this is possible? (I didn't believe I could even use an array formula as a named range?)
Secondly, is it possible to shorten the formula?
Thanks, Andy.

[HansV]

The formula =Uniques is location-dependent: it looks at the cells above it. By itself it returns only a single value, not a range.

I'd use VBA, see for example Spreadsheet Page Excel Tips: Identifying Unique Values In An Array Or Range.

[agibsonsw]

Thank you.
Actually, as I'm placing the formula down a column anyway I can use this column as the input range for the combo box.
Regards, Andy.

[agibsonsw]

Hi again.
I would like some help understanding the above formula - It's not mine! Specifically, why does
{=COUNTIF($B1:B5,$A$2:$A$12)}
yield 1s and 0s when I highlight it and press F9? I believe it to be looking for the values in column A that are in column B, but if the value occurs more than once it
still returns 1 rather than 2 or 3 etc. That is, why is it not counting?
Thanks, Andy.

[sdckapr]

How do you have it setup exactly?

When I press <F9>, I get an array of 11 items: that looks something like:
{={3;1;1;0;0;0;0;0;0;0;0}}

the first is the number in B1:B5 matching the value in A2, then the number in B1:B5 matching the value in A3, etc

Steve

[HansV]

Normally, the second argument of COUNTIF is a single condition; if this condition is a value, COUNTIF returns the number of times this value occurs in the range (the first argument).
Here, the second argument is an array (range) of 11 values, hence COUNTIF($B1:B5,$A$2:$A$12) returns an array of 11 counts: { how many times does A2 occur in B1:B5, how many times does A3 occur in B1:B5, ..., how many times does A12 occur in B1:B5 }.
Assuming that the values in B1:B5 are unique, each count will be 0 (if the value of An doesn't occur in B1:B5) or 1 (if the value of An does occur in B1:B5). So you'll get something like {0,1,1,0,0,0,1,0,0,0,0}

[agibsonsw]

Ah! The penny's dropped.
Because we're looking for the appearance of column A values in column B (which will only contain unique values) the count will always be 1 or 0.
Excellent. Ta, Andy.

[agibsonsw]

How do you have it setup exactly?

When I press <F9>, I get an array of 11 items: that looks something like:
{={3;1;1;0;0;0;0;0;0;0;0}}

the first is the number in B1:B5 matching the value in A2, then the number in B1:B5 matching the value in A3, etc

Steve

I've inserted a couple of rows, but data is in A5:A15. I place a title in E4 and the following is entered as an array formula in E5 and then dragged down:
=IF(ISNA(INDEX($A$5:$A$15,MATCH(0,IF(ISBLANK($A$5:$A$15),"",COUNTIF(E$4:E4,$A$5:$A$15)),0))),"",INDEX($A$5:$A$15,
MATCH(0,IF(ISBLANK($A$5:$A$15),"",COUNTIF(E$4:E4,$A$5:$A$15)),0)))
The COUNTIF yields only 1s or 0s because it is looking for the values A5:A15 in the (unique) values E5:E5, E5:E6 etc as explained by Hans.
Andy.