Comparing two arrays and remove values that appear in both

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.

ARRAY_DIFF(array 1, array 2)

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

ARRAY_EACH(array, variable, FUNCTION())

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]

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

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

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.

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

Nothing yet, @Sean_Harrington.

Add your “Like” here:
New Formula Field Functions

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:

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

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:

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.

ARRAY_DIFF(array 1, array 2)

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

ARRAY_EACH(array, variable, FUNCTION())

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]

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

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

Nope – that post was a #product-suggestions for Airtable. None of the stuff I posted up there can be done yet.

+1 for this, I’m stuck on the same issue (and same use case).

Here here I’d like to see this.

Need this so bad! Arrayunique is flawed outside of Rollups…

This is a really essential feature for me…

Me too, would be great to be able to have the inverse as well, eg: finding linked records that meet different criteria.

You could create a table with both sets of data (including duplicates) then use the “Dedupe” app to remove the ones you don’t want.

I can’t believe this hasn’t been implemented yet - FOUR AND A HALF years later

Yes, I’d like this too - Need an easy way to generate an email list that omits people who have already responded…

Difference between airtable and spreadsheet is that each airtable formula is array function itself, applied to a vertical column of data.
Like in other databases, similar types of data located in column, so variety of array functions can be used as native feature, without special formulas.

I have some tables containing 50k records and formula applied to rollup function which works quite fast, I mean it changed in realtime despite each of 50k values passes through loop comparing with other 50k list. It’s 2.5 billion operations. Of course, alghorhytm optimized and it uses less loop cycles.
But imagine Airtable has such “horizontal” formula an I put it to field. So it should count these 2.5*10^9 operations for each of 50k rows and do it in realtime. I don’t think it’s possible. At least for multi-user cloud service.

Another year not implemented