Finding partial entries from one list

Copper Contributor



I'm having the following issue and can't figure out what would be the best the way to resolve it.


I have column A that has file names such as









and column B that has 





The actual columns have actually much more but that gives the idea of what I have going on.


What I need to do is find out which filenames from Column A match the base names from column B, and which ones don't match.  My end goal is to get a list of all of the filenames that don't match. so in the above example I would need something that tells me 12346alt1.png doesn't have a match of 12346 in my column. For reference column A has 200k+ lines and column B has like 53k+ lines. so basically impractical to do it one by one.


Basically the best way to explain it is Column A is a list of our images and Column B is a list of what items those images refer to we still sell. I need to get rid of the images we no longer sell so I need a List of Column A filenames that do not match Column B active list.


Let me know if any other information would help out! 


Edit: Made a quick edit as I realized I made a mistake in my example, and wasn't too clear.




4 Replies


=IF(SUMPRODUCT(N(ISNUMBER(SEARCH(B1,$A$1:$A$200000)))),"","not in column A")

You can try this formula.

partial match.png

Hey! thanks for the reply, sorry I had made some mistakes in my original post and wasn't as clear as I could have been. I edited the post to include more information. Sorry about that.


=IF(SUMPRODUCT(N(ISNUMBER(SEARCH(LEFT(A1,5),$B$1:$B$3)))),"","not in column B")

For .png which start with 5 numbers you can apply this formula.

finding partial match.png