Forum Discussion
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...
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)) )
- 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 againI 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)- 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)))
