# Finding partial entries from one list

Copper Contributor

# Finding partial entries from one list

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!

4 Replies

# Re: Finding partial entries from one list

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

You can try this formula.

# Re: Finding partial entries from one list

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.

# Re: Finding partial entries from one list

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