Finding data not on a list

Hi guys, I'm using Windows 10 and Office 365. I'm looking to try and find all the date that is not on a different list. I have 2 lists of names, 1 which is split into First and Surname (say c...

office 365

training

PeterBartholomew1

Silver Contributor

May 27, 2021

ExcelLearner2395

MATCH (or these days XMATCH provides a good way of locating missing names

= LET(
  missing, ISNA(XMATCH(Name, First&" "&Last)),
  FILTER(Name, missing) )

(only the presentation is different).

To do the same thing with COUNTIFS requires greater effort. One way round the formula could be

= LET(
  space, SEARCH(" ", Name),
  fName, LEFT(Name, space-1),
  sName, MID(Name, space+1,10),
  found, COUNTIFS(First, fName, Last, sName),
  FILTER(Name, NOT(found)))

and the reverse

= LET(
  matches,  COUNTIFS(Name, First&"*", Name, "*"&Last),
  FILTER((First&" "&Last),  NOT(matches)))

Just for fun, I generated the list of names that appeared in either list but not both by defining a Lambda function VSTACKλ to equal

= LAMBDA(A₁,A₂,
   LET(
    m₁, ROWS(A₁),
    m₂, ROWS(A₂),
    k, SEQUENCE(m₁+m₂),
    IF(k<=m₁, INDEX(A₁, k), INDEX(A₂, k-m₁)))
   )

so the formula on the worksheet is given by

= LET(
  combined, VSTACKλ(First&" "&Last, Name),
  UNIQUE( combined, , 1) )

PeterBartholomew1

Silver Contributor

May 27, 2021

I have just seen the requirement to split the names

 = LET(
   space, SEARCH(" ", Name),
   fName, LEFT(Name, space-1),
   sName, MID(Name, space+1,10),
   found, COUNTIFS(First, fName, Last, sName),
   table, IF({1,0}, fName, sName),
   FILTER(table, NOT(found)))

Forum Discussion

Finding data not on a list