Create a unique list from a column and filtering some values

[mixy]

Hi all,

I have created a unique list of countries using a example from this link How to extract a unique distinct list from a column in excel | Get Digital Help - Microsoft Excel resource

This is my formula array:
{=INDEX(Countries, MATCH(0, COUNTIF($K$1:K1, Countries), 0))}

where Countries is a named range for column A data. For example:
Spain
USA
USA
France
Spain

The result of the formula will be:
Spain
USA
France

Do you know how to discard, for example, Spain from the calculation?
In other words, find me the unique countries of except Spain.
In the above example, that would be USA and France

Thanks!!!

mixy

 

[T. Valko]

Hi all,

I have created a unique list of countries using a example from this link How to extract a unique distinct list from a column in excel | Get Digital Help - Microsoft Excel resource

This is my formula array:
{=INDEX(Countries, MATCH(0, COUNTIF($K$1:K1, Countries), 0))}

where Countries is a named range for column A data. For example:
Spain
USA
USA
France
Spain

The result of the formula will be:
Spain
USA
France

Do you know how to discard, for example, Spain from the calculation?
In other words, find me the unique countries of except Spain.
In the above example, that would be USA and France

Thanks!!!

mixy

Like this...

Still array enterd:

=INDEX(Countries,MATCH(0,IF(Countries<>"Spain",COUNTIF($K$1:K1,Countries)),0))

 

[Teethless mama]

=INDEX(Countries,MATCH(0,IF(Countries<>"Spain",COUNTIF(K$1:K1,Countries)),0))

Ctrl+Shift+Enter, not just Enter
copy down as far as needed

 

[mixy]

Like this...

Still array enterd:

=INDEX(Countries,MATCH(0,IF(Countries<>"Spain",COUNTIF($K$1:K1,Countries)),0))

Thanks T.Valko!
That worked perfectly!

cheers

 

[T. Valko]

Thanks T.Valko!
That worked perfectly!

cheers

You're welcome. Thanks for the feedback!