Excel Formula - Count the number of unique values a vlookup would return

[DanAuber]

Hi there,

I'm struggling to write an excel formula to do what I want to do. I have a list of Countries and each is supposed to have a unique rating in the list. I want to put a formula in to check this. In a perfect world the formula would come back with the number of unique records for the country (and then I could investigate all those over 1). However if this is too tricky, I'd be happy if it just returned "Non-Unique"). I've attached a small data sample along with the two possible columns I'm trying to generate in yellow.

Thanks for any help on this

 

[SkipVought]

Hi,

A simple solution that indicates either you have one rating or you don't, seems sufficient.

I made your table a Structured Table named tCntryRtg. This expression compares the occurrence count of Country & Rating with the occurrence count of Country alone...
[tt]
=SUMPRODUCT(--([Country]=[@Country])*([Rating]=[@Rating]))=SUMPRODUCT(--([Country]))
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]