excelexcel-formulacompareinformation-extraction

Is there a way to extract unmatched data from a cell string in excel?

I have been given a excel file which contain columns, and within each cell of the column there are multiple entries separated by commas, as

Column 1 Column 2 Column 3
A1, A7, A11, B12, B15 A1, A7, A11, B12, B15, C2, C58, C9 A7, A11, B12, B15, C2, C58
A6, B8, C23, D19 A6, C23, D19 A6, B8, B12, C23, D19

I am trying to compare the cells in column 2 and 3 with the cell of the same column in row 1 and extract any additions to the original string, ideally to give an output like this:

Column 1 Column 2 Column 3 C1 vs C2 C1 vs C3
A1, A7, A11, B12, B15 A1, A7, A11, B12, B15, C2, C58, C9 A7, A11, B12, B15, C2, C58 C2, C58, C9 C2, C58
A6, B8, C23, D19 A6, C23, D19 A6, B8, B12, C23, D19 0 B12

So the comparison is only 1 way, and things which are present in column 1 and not in columns 2 or 3 are not reported, only things which have been added.

I have found some links online which suggest ways of potentially highlighting differences but none which can extract the additions.

It would be possible to convert the strings to individual cells and then compare whole rows/columns but there are hundreds of entries so I think this would be very inefficient. (Similar to Is there a way to find if a cell contains same strings to other cells?)

Solution

  • For 365 (or online) there are probably lots of ways. Here is one (copy across and down):

    =LET(a,TEXTSPLIT(B2,,", ",TRUE),b,TEXTSPLIT($A2,,", ",TRUE),IFERROR(TEXTJOIN(",",TRUE,FILTER(a,BYROW(a,LAMBDA(c,ISERROR(MATCH(c,b,0)))))),""))

    formula demo