Conditionally selecting two columns

Question

Here's the Data in my spreadsheet:

colA   colB
NYC    USA
NYC    USA
ROC    USA
SEA    USA
YVR    CAN
YYZ    CAN
LON    UK
LON    CAN
LON    USA

How do I get the data for the following rule:

Get all the cities and countries, for which there is more than 1 unique combination of city+country. In other words, extract only those cities (along with respective countries), which have more than one country.

From the above data, I'm looking to get the following result:

LON  UK
LON  CAN
LON  USA

because LON is the only one which occurs more than 1 once in different countries.

Tried COUNTIF, UNIQUE, QUERY and none of them produce the result I hope for, because I know I'm not using them correctly.

Answer 1

You need to use QUERY function as

=QUERY("SELECT ColA, ColB WHERE Col1='colA'"
  QUERY(
       QUERY(
             Data!A1:B10,
            "SELECT colA, count(distinct ColB) GROUP BY ColA"
            ), 
        "WHERE Col2 > 1")
      )

where Col2 is case-sensitive and should be written as initcapped as this.

Answer 2

In SQL, you would get the list of such cities by doing:

select city
from t
group by city
having min(country) <> max(country);

However, if you want the original rows, I would instead use exists:

select city, country
from t
where exists (select 1 from t t2 where t2.city = t.city and t2.country <> t.country);