How to search substring against row and if duplicates found, remove them?

**joshman10888** · 09-03-2016, 07:44 PM

Desired logic:
If O2="true" then search (substrings) in e2 against e:e and if (substrings) in e2 are found elsewhere in e:e then delete said substrings from e2, else do nothing, leave cell contents as is.

e2 looks like:

http:// ec2-54-172-158-94.compute-1.amazonaws.com/assets/images/bob/asdf1.jpg;http:// ec2-54-172-158-94.compute-1.amazonaws.com/assets/images/bob/asdf2.jpg;http:// ec2-54-172-158-94.compute-1.amazonaws.com/assets/images/bob/asdf3.jpg

I have inserted spaces after the // to preserve formatting. The semicolon ";" separates substrings. In this case, e2 has the three unique substrings of:

http:// ec2-54-172-158-94.compute-1.amazonaws.com/assets/images/bob/asdf1.jpg

http:// ec2-54-172-158-94.compute-1.amazonaws.com/assets/images/bob/asdf2.jpg

http:// ec2-54-172-158-94.compute-1.amazonaws.com/assets/images/bob/asdf3.jpg

What I do know:
1 The amount of substrings in each cell in range e:e are not guaranteed to be the same. Cells may sometimes be blank but will never exceed 12 substrings.
2)Substrings will ALWAYS start with the http:// file path up UNTIL the word "bob".

Ask any questions! Thanks! I asked over on mrexcel and couldn't find an answer. I googled but my knowledge of vba isn't sufficient.

**mikerickson** · 09-03-2016, 08:18 PM

Could you attach a workbook showing some representative data and desired results?

If I read your question correctly, you want this macro to act on E2 only and not change the contents of the other rows of E.

The if O2=True is a bit puzzling. Are you looking for this macro to be triggered by the user entering True in O2, or are you looking for a spreadsheet formula that will return the reduced value from E2 into some other cell? Something like =IF(O2, reduced(E2), E2)

**joshman10888** · 09-03-2016, 10:00 PM

Originally Posted by mikerickson

Could you attach a workbook showing some representative data and desired results?

If I read your question correctly, you want this macro to act on E2 only and not change the contents of the other rows of E.

The if O2=True is a bit puzzling. Are you looking for this macro to be triggered by the user entering True in O2, or are you looking for a spreadsheet formula that will return the reduced value from E2 into some other cell? Something like =IF(O2, reduced(E2), E2)

Im out right now so i cant attach sorry. Yes I should clarify. Basically there are cells in the E:E range that contain duplicate substrings. So for instance there might be duplicates in e2 and e4 but I have identified which of the cells ought to have the duplicates deleted by marking the associated O cell as TRUE. The range might be several thousand rows and any may contain duplicates that need to be removed. So e192 may have duplicates that are in e1356. We know which cell to target for deletion by looking at the O values for those rows. If O1356 is marked true then the duplicates found in the corresponding e1356 ought to be removed and e192 should stay.

So every cell in range E is subject to be searched for duplicates against the range and removed if the associated O value is true. Hope that makes sense.