How do I go about removing values found in one cell out of another cell?

Question

I have an Excel sheet where I have two columns with certain "keys" in every cell, like column A cell 1 would look like

|55|56|60|99|120| and column B cell 1 would look basically the same but just with different values, like |55|56|62|100|121|130|210|312|.

Now what I need is to remove every occurrence in column A cell 1 that also occurs in column B cell 1 and so on. In the end I want to have a third cell that only shows "|60|99|120|".

I cannot find anything online that tells me how to "compare" two cells with each other and remove duplicates. All I can find is the filter option that would compare cells in column A and remove the ones that have appeared exactly like that before, but that is not what I need. What I did find was a VBA module that almost did what I wanted, but it only removed one instance of the duplicate and kept the original.

I need to do this for about 450 columns, so doing this manually would be a chore and it would help with future tasks.

Answer 1

Try:

Formula in C1:

=REDUCE(A1,TEXTSPLIT(B1,"|",,1),LAMBDA(x,y,SUBSTITUTE(x,"|"&y&"|","|")))

---

Explanation:

You can look at LAMBDA() as an iterative process. The function is available as a stand-alone function for users, but MS made several lambda-helpers available that utilize this iterative concept. One of such functions is REDUCE().

This function specifically could be seen as an iterative process where a starting input is altered and saved upon each iteration. In this specific case our 1st parameter is the content of cell A1. Our 2nd parameter holds an array, obtained through splitting the content of cell B1 by the delimiter '|'. The 3rd parameter holds the actual iterative lambda process where we first named our variables (our first two parameters).

Thus within each iteration the variable 'x' holds the output of the last iteration, and variable 'y' holds the next element in line from our array of numbers. Another way of looking at it would be to think of an accumulator and a current value if you are more familiar with those concepts.

To visualize this a little bit:

i	X	Y	New_X
1	|55|56|60|99|120|	55	|56|60|99|120|
2	|56|60|99|120|	56	|60|99|120|
3	|60|99|120|	100	|60|99|120|
4	|60|99|120|	121	|60|99|120|
5	|60|99|120|	130	|60|99|120|
6	|60|99|120|	210	|60|99|120|
7	|60|99|120|	312	|60|99|120|

Here 'New_X' is generated through SUBSTITUTE(X,"|"&Y&"|","|") and is going to be the input in the next iteration.

---

Note: OP mentioned he shared the wrong dummy data where all double pipe-symbols should be swapped out for single ones.