Comparing two arrays and remove values that appear in both


#1

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?


Removing specific values from arrays
If() with and() or()
New Formula Field Functions
#2

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 intersect formula
Dynamic (calculated) link to a record in a linked table
Look-Up from a Look-Up Field
How can I make a lookup field show only unique results?
Array intersect formula
#3

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




#4

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.


#5

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


#6

Nothing yet, @Sean_Harrington.

Add your “Like” here:
New Formula Field Functions