Who went to both USA & UK? [Excel Challenge]

13 Responses to “Who went to both USA & UK? [Excel Challenge]”

1. Matej says:

   November 10, 2023 at 6:35 am

   Filter for USA & UK in col 2, remove col 2, keep duplicates col 1, remove duplicates col 1.

   Reply
2. BDT says:

   November 10, 2023 at 1:02 pm

   Is it cheating to use PY formulas?
   ```python
   focal_locs: list[str] = ["UK", "USA"]
   df_trips = xl("trips[#All]", headers=True)
   df = df_trips.drop_duplicates().query('Country in @focal_locs')
   recs_both = df[df['Employee'].duplicated()]
   recs_both['Employee'].sort_values()
   ```

   I started to do an Excel solution, but need to run to a meeting. It wasn't as concise as yours anyway!
   ```
   =LET(
   focalLocs,{"USA","UK"},
   allEmps, UNIQUE(trips[Employee]),
   blnEmpsWentToEither, ISNUMBER(XMATCH(trips[Country],focalLocs)),
   recsWentToEither, FILTER(trips,blnEmpsWentToEither),
   uniqRecsWentToEither, SORT(UNIQUE(recsWentToEither)),
   empsWentToEither, CHOOSECOLS(uniqRecsWentToEither,1),
   empsWentToEither
   )
   ```
   Just need to return the dups from `empsWentToEither`.

   Reply
3. Rasmus says:

   November 10, 2023 at 3:27 pm

   Power Query solution based on Group By and List.Contains.

   let
   Source = Excel.CurrentWorkbook(){[Name="trips"]}[Content],
   #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Country", type text}}),
   #"Grouped Rows" = Table.Group(#"Changed Type", {"Employee"}, {{"Countries", each _, type table [Employee=nullable text, Country=nullable text]}}),
   #"Added Custom" = Table.AddColumn(#"Grouped Rows", "CountriesList", each Table.Column([Countries], "Country")),
   #"Added Custom1" = Table.AddColumn(#"Added Custom", "Went to both UK and USA", each if List.Contains([CountriesList], "UK") and List.Contains([CountriesList], "USA") then "Went to both UK and USA" else "Did not go to both UK and USA")
   in
   #"Added Custom1"

   Reply
4. Ranjith says:

   November 13, 2023 at 12:32 am

   Does this require a formula or power query ? A simple pivot table added to data model will allow a count distinct output. A country filter on US & UK should return the two employees.

   Reply
   • Chandoo says:

     November 13, 2023 at 3:42 am

     Good one 🙂 Of course, formula / pq / dax options will give you flexibility to parameterize the country list. That is all.

     Reply
5. Giancarlo says:

   November 13, 2023 at 10:18 am

   Hi

   =TOCOL(UNIQUE(BYROW(trips,LAMBDA(k,@FILTER(trips[Employee],IF(INDEX(k,,2)="UK",(trips[Employee]=INDEX(k,,1))*(trips[Country]="Usa")))))),3)

   Reply
6. Daniel H says:

   November 13, 2023 at 5:51 pm

   Another one, very elegant in my opinion.

   =LET(
   e, trips[Employee],
   c, trips[Country],
   one, UNIQUE(FILTER(e, c = "USA")),
   two, UNIQUE(FILTER(e, c = "UK")),
   combo, XLOOKUP(one, two, two, ""),
   SORT(FILTER(combo, combo ""))
   )

   Reply
7. Daniel H says:

   November 13, 2023 at 5:59 pm

   In the last filter, "less than" and "more than" signs are gone.

   Same formula, this time not using the missing signs:

   =LET(
   e, trips[Employee],
   c, trips[Country],
   one, UNIQUE(FILTER(e, c = "USA")),
   two, UNIQUE(FILTER(e, c = "UK")),
   combo, XLOOKUP(one, two, two, ""),
   SORT(FILTER(combo, NOT(combo = "")))
   )

   Reply
8. Eleuterio says:

   November 14, 2023 at 9:49 am

   I've translated from italian language, maybe some separators need to be changed, but is very short:
   =LET(
   E, trips[Employee],
   UNIQUE(
   FILTER(
   E,
   MMULT(
   --(
   COUNTIFS(
   E, E,
   trips[Country], {
   "UK",
   "USA"
   }
   ) > 0
   ),
   {1;1}
   ) > 1,
   ""
   )
   )
   )

   Reply
9. David N says:

   November 15, 2023 at 9:43 pm

   =LET(
   usa,UNIQUE(FILTER(trips[Employee],trips[Country]="USA")),
   uk,UNIQUE(FILTER(trips[Employee],trips[Country]="UK")),
   both,FILTER(usa,ISNUMBER(XMATCH(usa,uk))),
   SORT(both)
   )

   Reply
   • David N says:

     November 15, 2023 at 9:52 pm

     Or...

     =LET(
     emp,UNIQUE(trips[Employee]),
     num,MMULT(SIGN(COUNTIFS(trips[Employee],emp,trips[Country],{"USA","UK"})),{1;1}),
     SORT(FILTER(emp,num=2))
     )

     Reply
10. tetonne says:

    November 19, 2023 at 11:25 am

    Hi, could be great to add min Excel version required.

    Reply
11. Stéphane says:

    November 24, 2023 at 2:11 pm

    LET(
    UK_USA,TAKE(UNIQUE(FILTER(trips,(trips[Country]="UK")+(trips[Country]="USA"))),,1),
    UNIQUE(VSTACK(UNIQUE(UK_USA),UNIQUE(UK_USA,,TRUE)),,TRUE))

    Reply

Leave a Reply