CODEWITHSUNDEEP
excelexcel-formulacompareinformation-extraction
beanie42
beanie42

Reputation: 71

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?)

Upvotes: 0

Views: 342

Answers (2)

user22566114
user22566114

Reputation: 391

I can't yet add a screenshot since i'm a new user. The data is in range A2:A3 (Column1), range B2:B3 (Column2) and range C2:C3 (Column3).

This formula is in cell D2 and spills the results for C1 vs C2:

=IFERROR(MAP(A2:A3,B2:B3,LAMBDA(y,x,TEXTJOIN(", ",,FILTER(TEXTSPLIT(x,", "),BYCOL(TEXTSPLIT(x,", "),LAMBDA(c,NOT(ISNUMBER(SEARCH(c,y))))))))),0)

This formula is in cell E2 and spills the results for C1 vs C3:

=IFERROR(MAP(A2:A3,C2:C3,LAMBDA(y,x,TEXTJOIN(", ",,FILTER(TEXTSPLIT(x,", "),BYCOL(TEXTSPLIT(x,", "),LAMBDA(c,NOT(ISNUMBER(SEARCH(c,y))))))))),0)

Upvotes: 0

Rory
Rory

Reputation: 34075

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

Upvotes: 1

Related Questions