#43 - Find the Difference Between Two Strings

Judith-Excel-Sharing - Aug 13 - - Dev Community

Problem description & analysis:

In the Excel table below, both column A and column B contain semicolon-separated strings:

original table

Task: Find the difference between column A and column B, which consists of items that are contained in column A but not contained in column B, and separate the result with the semicolon:

desired table

Solution:

Use SPL XLL to get this done:



=spl("=?.((~1.split($[;]) \ ~2.split($[;])).concat($[;]))",A1:B3)


Enter fullscreen mode Exit fullscreen mode

As shown in the picture below:

result table with code entered

Explanation:

~1 represents the first child member of the current member (row); \ is the operator for computing the difference; $[] represents a string.

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .