Forum Discussion
Finding data not on a list
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 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))))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.
Oh, why do we need lambdas here?
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 Lorenzo picture, though I had moved the range so my cell references would not be the same even if I quoted them.
