Highlight the rows that have same B and C - but different A

[suzie_blue]

Hello everybody,

I've been trying to find a formula in Excel for months - and I can't find anything that works. I hope someone can help me.

Here is what I would like to do:
I have 3 columns A (ID), B (Country) and C (City)
I would like to delete the rows that have the same B (Country) and C (City) - but that have different A (ID)
In the excel sheet, I have B=Spain et C=Barcelona, but the combination B&C have two different IDs (A and B). So I would like to quickly identify that I have to delete these rows.

I've tried something with "=IF(OR(AND(SUMPRODUCT" - but it didn't work since some results to the formula were blank.

Does someone have any ideas about a formula to resolve this?

I usually have 200 IDs, so doing this manually is a real hustle.

Please find attached the test excel file

Thanks,
suzie blue

 

[ETAF]

you could use
=COUNTIFS($A$2:$A$20,A2,$B$2:$B$20,B2,$C$2:$C$20,C2)=COUNTIFS($B$2:$B$20,B2,$C$2:$C$20,C2)

But some of the I had "I " the label I and a space - so did not match

you may need to add TRIM, to the range
what version of excel do you have

this will give true false

but you can use in an IF(

=IF(COUNTIFS($A$2:$A$20,A2,$B$2:$B$20,B2,$C$2:$C$20,C2)=COUNTIFS($B$2:$B$20,B2,$C$2:$C$20,C2),"Keep","Remove")

 

[suzie_blue]

you could use
=COUNTIFS($A$2:$A$20,A2,$B$2:$B$20,B2,$C$2:$C$20,C2)=COUNTIFS($B$2:$B$20,B2,$C$2:$C$20,C2)

But some of the I had "I " the label I and a space - so did not match

you may need to add TRIM, to the range
what version of excel do you have

this will give true false

but you can use in an IF(

=IF(COUNTIFS($A$2:$A$20,A2,$B$2:$B$20,B2,$C$2:$C$20,C2)=COUNTIFS($B$2:$B$20,B2,$C$2:$C$20,C2),"Keep","Remove")

WOW! Unbelievable it works! Thank you very much!

 

[ETAF]

you are welcome