Finding partial text matches compared to a range

Hi everyone,

I'm having a heck of a time searching for this, I think I'm probably just using the wrong terms.

I have one list of confirmed IDs that I need to compare against a list of original IDs so that I can remove those that confirmed from the original ID list. The confirmed ID list is just a portion of the text string given in the original list, so wildcards are needed.

I'm tripped up on how to compare a value to a range of possible matches. I know how to use things like countif to compare a list against a single value, but I don't know how to compare a cell against a range of values.

Before:
ExcelExample_before.xlsx

Desired after:
ExcelExample_after.xlsx

Any ideas?

Thanks!

If you are removing IDs from the confirmed ID list, use this formula to ID values that exist within the Original ID batch. The resulting number tells you which row within the Original ID the value resides:

Please Login or Register  to view this content.

Enter into cell B2 and drag down.

The other way around. The values from the confirmed ID list need to be removed from the Original ID list (as we'll be recontacting those on the original list that have not confirmed). So it's the Original ID list that will be getting edited based on the matches.

Your formula does return the correct row number where there's a match, but it looks like I'd still have to manually go to each row and delete to original ID. The actual list I'm working with is over 17,000 values, so I'm hoping to find a solution that lets me then filter on the formula result and delete all matches in one fell swoop.

Originally Posted by judgeh59

try this in row D

Please Login or Register  to view this content.

Thanks! Almost there but have an odd error when I try to apply it to the actual file where it's not returning any values.

The full values in Original ID are actually URLs - I deleted most of the URL in the example for brevity and data confidentiality. So "S01g_Hh_&ps=PPo9uH1pwESJZLdcHm0H9Q" would actually be "https://www.websitename.com/R.aspx?o=n_S01g_Hh_&ps=PPo9uH1pwESJZLdcHm0H9Q" It seems the presence of two = symbols is throwing it off. I tested by deleting the first = and then it returned a positive match.

I see your formula is possibly looking for an = sign, any thoughts on how to get around this?

Bingo, that did it! Many thanks and rep!