Finding Partial Match in Two Different Columns in Excel

Question

I need to find a partial match in two different columns in excel, then highlight the values. Once the values are highlighted I need to be able to filter the results. If you use conditional formatting, highlight duplicate value rule it only captures exact matches. As you can see some of my values have a "-" in them and using the conditional formatting it doesn't highlight those values.

The two column I need compared are A and F, values won't match by row. I could have a 361569 (A61) in column F in a totally different row.

Anyone have any ideas how to do this?

Answer 1

A COUNTIF/COUNTIFS with a wildcard seems the logical solution.

=COUNTIF(F:F, A1&"*")

Is there something I'm not recognizing that makes that impractical?

Answer 2

Apply

H1:  =COUNTIF(F:F,F1)+COUNTIF(F:F,F1&"*")

and drag down.

You are interested in all rows with values > 1.

If you apply

I1:  =F1&IF(ISERROR(FIND("-",F1)),"-00","")

and drag down, you can sort the data by this column and get the matching rows below each other.

Andreas.

Answer 3

I need to compare the values by columns, not by row. I want to find the values in column A and column F that have the first 6 digits matched then highlighted. Does that make sense?

Answer 4

Fill couple of answers against the matching/non-matching values in the rightmost column. Press Ctrl+E (flashfill), excel will give results against rest of comparisions. Once you have results, conditional matching can be done easily

Answer 5

I need to compare the values by columns, not by row. I want to find the values in column A and column F that have the first 6 digits matched then highlighted. Does that make sense?

Same formulas as I've posted, just replace F1 with A1.

Andreas.