Argthtjtr

Here's a 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.

Thank you.

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);

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.