Problem trying to get the unique values from a column

[cyd44]

Hi,

I have a worksheet with a column holding types for each record. I want to get a list of unique values from this column and am using the formula

=INDEX($A$1:$A$478,MATCH(0,COUNTIF($S$1:S1,$A$1:$A$478),0))

I am getting no error message from the formula but the cell just contains #N/A

when I look at the results, it appears that the INDEX part works OK but am getting no results from thr MATCH/COUNTIF section.

I am using Access 2010.

Could anyone please advise?

 

[boblarson]

Hi,

I have a worksheet with a column holding types for each record. I want to get a list of unique values from this column and am using the formula

=INDEX($A$1:$A$478,MATCH(0,COUNTIF($S$1:S1,$A$1:$A$478),0))

I am getting no error message from the formula but the cell just contains #N/A

when I look at the results, it appears that the INDEX part works OK but am getting no results from thr MATCH/COUNTIF section.

I am using Access 2010.

Could anyone please advise?

Don't you mean you are using EXCEL 2010? Access doesn't use what you've posted.

 

[cyd44]

Don't you mean you are using EXCEL 2010? Access doesn't use what you've posted.

Sorry Bob, Yes I do mean Excel!

 

[NBVC]

Are you confirming the formula using CTRL+SHIFT+ENTER not just ENTER since it is an Array formula?

You can avoid the CSE confirmation by adapting formula as:

=INDEX($A$1:$A$478,MATCH(0,INDEX(COUNTIF($S$1:S1,$A$1:$A$478),0),0))


and also note that the formula must be in S2, copied down...