Save the date! Join us on October 16 for our Product Ops launch event. Register here.
May 07, 2018 12:09 PM
I’m trying to figure out how to remove values from an array in airtable:
I have two arrays created from two separate Rollups:
Array 1: A, B, C, D
Array 2: B, C
What I want to do is create a third array (or modify array number 1) where the values that exist in Array 2 are removed from Array 1. (Is there a general way to do this regardless of the size of the arrays? I have to this several times and the arrays are of different sizes)
The result: Array 3: A, D
Thanks for your suggestions!
May 07, 2018 12:27 PM
: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.
May 07, 2018 03:11 PM
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?
May 07, 2018 04:10 PM
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.
May 07, 2018 06:02 PM
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.