Finding partial entries from one list

[John_White690]

HI! 

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

12345alt1.png

12345alt2.png

12345alt3.png

12346alt1.png

12346alt2.png

12348.png

12348alt1.png

and column B that has 

12345

12347

12348

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.

Thanks!

[#2]

@John_White690 

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

You can try this formula.

[#3]

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.

[#4]

@John_White690 

=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.

 

[#5]

https://answers.microsoft.com/en-us/msoffice/forum/all/vba-code-to-compare-two-columns-in-two-sheets...

https://b23.tv/W7HYFwQ