Forum Discussion
John_White690
Sep 08, 2023Copper 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!
- OliverScheurichGold Contributor
=IF(SUMPRODUCT(N(ISNUMBER(SEARCH(B1,$A$1:$A$200000)))),"","not in column A")
You can try this formula.
- John_White690Copper ContributorHey! 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.
- OliverScheurichGold Contributor
=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.