Search code examples
excelstringmatchcelldelimiter

I have two cells that contain delimiter merged values and need to show if any of the values match when the two cells are compared

Comparing data changes from one year to the other. Person can have 5 IDs one year and two the next. Need to show if any of the IDs still match.

desired result:

desired result

  • J. Doe (cell1)4455667;443;3344;2345 |(cell2)443234;2345 |(result)One match found
  • R. Smith (cell1)1233;3234;45566 45566|(result)One match found
  • B. Frank (cell1)667890;989736;98965 |(cell2)989736;98965;998762 |(result)Two matches found
  • K. Dole (cell1)22345;555;345;543444 |(cell2)546444;90555 |(result)No match found

I have a script to find partial string matches but in this case I need to know if exact matches are found so that approach results in too much fall out. If I have to split the delimiter cells into multiple cells and do a match one by one the data and amount of formulas I would need to use would be too huge.

I expect to know if exact value matches are located within the cells that have merged delimited data when compared to another merged delimited cell.

Solution

  • For your sample data, assumed to be in A1:D5, insert four columns between the two years and after the second, split both years with Text to Columns, Delimited, Semicolon as delimiter and in L2 and copied down to suit:

    =COUNTIF(B2:E2,G2)+COUNTIF(B2:E2,H2)+COUNTIF(B2:E2,I2)