This Product Ideas board is currently undergoing updates, but please continue to submit your ideas.

Comparing two arrays and remove values that appear in both

Showing results for 
Search instead for 
Did you mean: 
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi airtable!

I have a list (an array) of items in two columns. Each row represents two different lists (one in each column). I’d like to be able to compare the two columns and remove what is common in both. For example

Row 1:
Column 1: Apple, Orange, Pineapple, Kiwi
Column 2: Apple, Kiwi

Result: Column 3: Orange, Pineapple

Row 2:

Column 1: Banana, Orange, Grape, Kiwi, Pineapple, Passion Fruit
Column 2: Orange, Grape, Pineapple

Result: Column 3: Banana, Kiwi, Passion Fruit

The general functionality is to be able to compare (two or more arrays) and perform some kind of action.

Is this something that could be worked on?

Let me know if you’d like more details.

There are a few other posts here from people who have similar requests and ideas:

Removing specific values from arrays (this is mine in support)
Array intersect formula
Using Rollup to check on completed tasks?

14 - Jupiter
14 - Jupiter

I would expand on this to say…

I’d like to see:

  1. The persistence of arrays in fields other than Rollups
    as the necessary pre-requisite to
  2. More powerful array functions that can be used in Formula fields

That’s my #feature-requests related to this concept.

A couple examples:

ARRAY_DIFF(array 1, array 2)
  • Takes two arrays (or strings of objects with a separator that can easily be converted to an array) - returns {array 3} which contains all objects from {array 1} that do not also exist in {array 2} (ie, subtract {array 2} from {array 1})


ARRAY_DIFF([1, 2, 3, 4], [2, 4]) = [1, 3]

ARRAY_EACH(array, variable, FUNCTION())
  • Takes an array and a string Function - loops over the array and for each object, saves the object in the variable, and allows the user to use the variable in the chosen, compatible, string function (such as SUBSTITUTE(), or FIND()) or in logical Functions (such as IF(), OR(), AND())


ARRAY_EACH([1, 2, 3], variable, SUBSTITUTE([1, 3, 8, 2, 1, 4, 2], variable, "old"))
= ["old", "old", 8, "old", "old", 4, "old"]
array1 = [1, 2, 3, 2, 4]
{bad value} = 2
ARRAY_EACH(array1, variable, IF(variable = {bad value}, SUBSTITUTE(array1, variable, ""))
= [1, 3, 4]

For these to be even more powerful, it would be nice to have access to LOOKUP(table, field, linked-records-field) and ROLLUP(table, field, function) as functions within a formula field as well.

Ingredients Table has single-select field = {Kind}
Sandwiches Table has linked records field = {Ingredients}
In Sandwiches Table, show all Ingredients of {Kind} = “Vegetable”:

ARRAY_EACH({Ingredients}, variable, IF(LOOKUP(Ingredients, {Kind}, variable) != "Vegetable", SUBSTITUTE({Ingredients}, variable, "")))
= #for example [Tomato, Lettuce, Avocado]

So overall #feature-requests here is for more (and more powerful) functions to use in formula fields.

7 - App Architect
7 - App Architect

@Jeremy_Oglesby, please post this as its own feature request. Also, it would be great if you edited your answer so that the latter part is first (as it is more universally useful - imo).

This feature would also solve these related feature requests/support issues:

14 - Jupiter
14 - Jupiter

I’m working on the #feature-requests post. I’m going to create it as a list of all the functions we are requesting as users, as well as an aggregation of links to relevant posts, as you have started doing. Thank you for the idea and for doing the legwork of finding relevant posts!

I’ll send you a PM to notify you when it’s up.

4 - Data Explorer
4 - Data Explorer

Hi all,

Has there been any progress on this feature? It is exactly the one that I’m looking for. Any thoughts on if it will be added or has anyone figured out a workaround in the meantime?



14 - Jupiter
14 - Jupiter

Nothing yet, @Sean_Harrington.

Add your “Like” here:
New Formula Field Functions

4 - Data Explorer
4 - Data Explorer

I’m trying to understand how to write the formulas for Unique Array and exploring if I can use that with Roll up to solve this (because I have a similar issue).

First I used Concatenate to join all the food into one cell.

But when I go to the Rollup, I don’t know how to write the unique formula.

I wanted to add a link to my test table in here, but it won’t let me…so hopefully these images will come thru. And it will only let me do 1 image…

Airtable Array Unique help.JPG

I’m fairly new to using AT, so if I am way off - sorry… and if so, then maybe you can just explain more how ArrayUnique is supposed to work? Maybe give an example with words (I already watched the tutorial counting video and am a bit lost)

thanks :slightly_smiling_face:

13 - Mars
13 - Mars

Unfortunately, CONCATENATE() combines the values into a string, and there’s no
way to get Airtable to cast a comma-separated string to an array. (And, now I look more closely, your original arrays aren’t actually arrays – they’re strings, as well.)

I usually find myself tearing my hair out before I manage to find the mystical syntax and environment that gets ARRAYUNIQUE() to play nicely. I think, assuming you did have two authentic arrays — for instance, two linked-record fields, which Airtable reports back as an array, you could try

4 - Data Explorer
4 - Data Explorer

Thanks. You are correct in all your notes. I don’t really know what an array is. I googled it and I’m just getting definitions of data.

So what is it and how do I create one inside AT?

I can put my data however it needs to be. If we don’t want commas, we can take them out.

  • hopefully I don’t have to put each word in a column because I’m looking at like 1,000 words in a cell

So, how do I make my Column 1 and 2 be arrays and not strings?
*Thank you for taking time for me :slightly_smiling_face:

4 - Data Explorer
4 - Data Explorer

Any update on even elementary array functions please? Intersection, difference, union,…

These are all one-liners in ES6, see

Alternatively, any chance to give us more freedom in the formula column, maybe to use our own plain-vanilla simple javascript functions, just feeding them with data from other columns/tabs?

Another alternative: maybe a SQL dashboard block that would let you write your own custom query?

Is any of these in the pipeline, please, or are there other priorities? Airtable is awesome, but sometimes you just hit the wall and there’s not even a hacky workaround. Thank you.

4 - Data Explorer
4 - Data Explorer

The last part where you have Edit This cant be done yet can It?