Comparing two arrays and remove values that appear in both

[Phil_Miller]

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
etc.

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?

[Jeremy_Oglesby]

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})

Example:

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())

Examples:

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]

---

EDIT:
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.

[Matthew_Billio1]

@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:

Dynamic (calculated) link to a record in a linked table Ask the community

How to create a dynamic (calculated) link to another table? There is a Table#1 (Daily Expenses) [ID, Date, Sum, subject,MonthNo]. Where MonthNo=MONTH({Date}) There is a Table#2 (Months of the year) just as follows: MonthNo MonthName 1 January 2 February 3 March etc. Linking Table#1 to Table#2 will allow me to calculate monthly expenses directly in a Table#2 by means of Lookup(Sum) function. How can I do this? If I choose a standard Link field then every time later I will have to select…

How can I make a lookup field show only unique results? Ask the community

The result I am looking for is similar to the ARRAYUNIQUE function for a Rollup field. However, the results are a link to another table, and it seems that using a rollup can only show text results as opposed to clickable records. How can I make lookup field show only unique results, or make the unique results of a rollup field clickable?

Array intersect formula Product Suggestions

I have 2 tables, and I need a result that shows only inputs that are the same in both tables. Hence, to compute the intersection of a number of arrays. array intersect ( array1, array2 [, array#… ] ) Returns an array containing all the values of array1 that are present in all the arguments. Where: array1 The array with master values to check. array2 An array to compare values against. … A variable list of arrays to compare. Any workarounds at this point?

[Jeremy_Oglesby]

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.

[Sean_Harrington]

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?

Thanks!

Sean

[Jeremy_Oglesby]

Nothing yet, @Sean_Harrington.

Add your “Like” here:
New Formula Field Functions

[l_8UhqsWTYrwnqi]

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…

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:

[W_Vann_Hall]

l_8UhqsWTYrwnqikCQ0H:

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

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

ARRAY_UNIQUE(
    ARRAY_FLATTEN(
        {Array1},
        {Array2}
        )
    )

[l_8UhqsWTYrwnqi]

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:

[Josef_Richter]

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

These are all one-liners in ES6, see https://medium.com/@alvaro.saburido/set-theory-for-arrays-in-es6-eb2f20a61848

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.

[Fernando_santin]

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