#33 — Find The Differences Between Contents of Two Cells

#33 — Find The Differences Between Contents of Two Cells

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:

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

Solution:

Use SPL XLL to do this:

=spl(“=$[|] / (?1.split($[|]) ?2.split($[|])).concat($[|])/ $[|]”,A1,B1)

As shown in the picture below:


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.

Please follow and like us:
Pin Share