#33 — Find The Differences Between Contents of Two Cells

Judith-Excel-Sharing - Jul 25 - - Dev Community

Problem description & analysis:

In the following Excel table, cell A1 and cell B1 contain strings made up of vertical line-separated numbers and walled in by vertical lines at both ends:

original table

Task: find numbers that exist in A1 but does not exist in B1 and display them in the same format:

desired table

Solution:

Use SPL XLL to do this:

=spl("=$[|] / (?1.split($[|]) \ ?2.split($[|])).concat($[|])/ $[|]",A1,B1)
Enter fullscreen mode Exit fullscreen mode

As shown in the picture below:

result table with code entered
Explanation:

The symbol \ computes difference, which are members that are contained in set A but are not contained in set B. Symbol / concatenates two strings. $[] represents a string.

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