Finding data not on a list

[ExcelLearner2395]

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 column A and B) with around 1500 rows and another with first and surname in one column (say c) but over 3000 rows. Is there any way for me to compare the 2 lists to find the names which are MISSING, I can find the names which are on both but unable to find the names which are in the 3000 and not in the 1500.

Thanks for any help

[#2]

@ExcelLearner2395 

You begin with "I'm looking to try and find all the date" then you talk about names - a bit confusing :)

I assumed the First + Surnames in your column C are delimited with a <space> otherwise adapt

in D2

=FILTER(C2:C6, ISNA(MATCH(C2:C6,(A2:A4 & " " & B2:B4),0)) )

[#3]

Many thanks for this, sorry my wording was poor in the inital I meant to type "I;m looking to tra and find all the data" even though I meant names still. This is basically what I am looking for except I would need the results to be back into 2 columns - First and Last names seperately. Do you know if there is a way for me to do this?

Thanks again

[#4]

@ExcelLearner2395 

I don't see a way to dynamically split (via formula) the content of a cell (i.e. D1) in 2 columns. So the suggestion remains the same (you can hide column D)

in E2

=LEFT(D2#, SEARCH(" ",D2#)-1)

in F2

=MID(D2#, LEN(E2#)+1, 255)

[#5]

alternatively just reverse the ranges in the first formula from :
=FILTER(A2:B4,ISNA(MATCH(A2:A4&" "&B2:B4,$C$2:$C$6,0)))
if the names are hand typed you might also want to filter excess spaces:
=FILTER(A2:B4,ISNA(MATCH(TRIM(A2:A4)&" "&TRIM(B2:B4),$C$2:$C$6,0)))

[#6]

@ExcelLearner2395 

As variant

with

=LET(
  range,      A2:C10,
  first,      INDEX(range,0,1),
  last,       INDEX(range,0,2),
  both,       INDEX(range,0,3),
  included,   COUNTIFS(both, first & " " & last),
  bothMissed, FILTER(both, NOT(included)*(both<>"")),
  seps,       FIND(" ",bothMissed),
  firstMissed,LEFT(bothMissed,seps),
  lastMissed, RIGHT(bothMissed,LEN(bothMissed)-seps),
  IF({1,0}, firstMissed, lastMissed)
)

[#7]

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

[#8]

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

[#9]

@Peter Bartholomew  I liked your idea of finding the results bi-directional (especially since my first answer above completely missed the intended direction) so I did a version without Lambda:

=LET(m,$A$2:$B$50,n,$C$2:$C$50,
     s,ROWS(m),t,SEQUENCE(s+ROWS(n)),
     u,UNIQUE(IF(t<=s,INDEX(m,t,1)&" "&INDEX(m,t,2),INDEX(n,t-s)),0,1),
     CHOOSE({1,2},
          LEFT(SUBSTITUTE(u," ",REPT(" ",LEN(u))),LEN(u)),
          RIGHT(SUBSTITUTE(u," ",REPT(" ",LEN(u))),LEN(u))))

[#10]

@mtarler 

I get the impression that the LAMBDA function was only introduced as early as it was in the transformation of Excel because it leveraged the work already done for LET. The main difference is the reusability of the LAMBDA.

I accept it is a bit premature to introduce it into Q&As though. I have an alternative of using Charles Williams's FastExcel which has VSTACK and HSTACK functions but that makes the solution even more inaccessible. Mind you, Charles has now written a runtime version which I should investigate.

[#11]

@Peter Bartholomew 

The only point is to make a guess what is the Name, First etc here. In particular same size ranges or not, do you think that's obvious for everyone?

[#12]

@Peter Bartholomew 

Oh, why do we need lambdas here? 

[#13]

@Sergei Baklan 

As an answer for the OP, we don't.
For my own use, I would employ the FastExcel function VSTACK, and that was my starting point.
I based the names on @L z. picture, though I had moved the range so my cell references would not be the same even if I quoted them.