Removing specific values from arrays

:thinking: Unfortunately, not an easy task.

I’ve made #feature-requests elsewhere (I think…) about adding more array functions to give us the ability to do exactly these sorts of things.

Something like

ARRAY_DIFF({array1} ,{array2})

that returns a new array with the duplicates across either array removed would be fantastic.

If you have a relatively small set of values you are working with, you could try to isolate items from Array 2:

RIGHT({Array 2}, 2)

and then use that as the value to substitute with "" (nothing):

SUBSTITUTE({Array 1}, RIGHT({Array 2}, 2), "")

But this only works with highly predictable/controlled Array lengths and object orders.

ARRAY_DIFF({array1} ,{array2})

RIGHT({Array 2}, 2)

SUBSTITUTE({Array 1}, RIGHT({Array 2}, 2), "")

Thanks Jeremy

I understand what you are saying. It won’t work in this case so well because the arrays are not highly controlled or predictable (as you say).

The general case is something more like this where I have 2 columns of arrays and I want to compare the arrays in the two columns in each corresponding row:

Row 1:

Array in row 1 column 1: Apple, Banana, Lettuce, Cucumber, Brocoli
Array in row 1 column 2: Banana, Brocoli, Cucumber

Remove items in array in column 1 that are in array in column 2:

Result: Array in row 1 column 3: Apple, Lettuce

Row 2:

Array in row 2 column 1: Apple, Pineapple, Brocoli, Beet, Banana
Array in row 2 column 2: Apple, Banana

Remove items in array in column 1 that are in array in column 2:

Result: Array in row 2 column 3: Pineapple, Brocoli, Beet

Do you have a link to the feedback that you left so I can add mine to the corresponding thread?

Well, I finally found the post I made about that - it wasn’t an official feature request:

Perhaps I’ll try to make that into an official feature request thread. If you beat me to it, I’ll add my “Like” to it.

Hi Jeremy

I added it here: Comparing two arrays and remove values that appear in both

Lets see what happens - maybe you can also add some comments or use cases!

Thanks for your help today.