Count number of unique values in a list

[mikeymay]

I need to return a vlue that represents the number of unique string values in a list of data.

I know this can be done using pivot tables, but I would prefer to avoid doing this due to the increase in a size of the file.

Is it possible to create a string variable and assign the unique string value to the created string?


Thanks

 

[tigeravatar]

mikeymay,

If, say, the list of values are in A1:A100, the following will work:
=SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))

Hope that helps,
~tigeravatar

 

[mikeymay]

This is something I need to implement into some code but suppose this could be used in a Worksheet.Function bit of code??

 

[mikeymay]

Ok, I need to be able to do this programatically.

The code I am using scrolls down a list and needs to check each value to see if it is unique within a division label in the list.