Array intersect formula


#1

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?


New Formula Field Functions
Comparing two arrays and remove values that appear in both
Dynamic (calculated) link to a record in a linked table
Comparing two arrays and remove values that appear in both
#2

I don’t understand what you want to do. Why you have the same data in 2 Tables? Could you please explain deeper how is structured you Base and how the Tables are related?

I’m curious now hehe :thinking:


#3

Sure. Let’s say I have 4 tables with the unique records: Client, Industry, Problem, Solution. I input all the data, and the tables look like this:

In the Client table, I need a field that would return only the solution that covers Client industry and any of the Client problems. So I can look up for the solutions by the client industry and by the type of the problem. Then I need to marry them to find the best matching solution:

(To make it simpler, in this example a client is linked to only one industry, but in practice, a client could be linked to the multiple industries.)


#4

Did this make the question clear?


#5

I think you should work with the Solutions. I am not sure to have understood all the Base (I don’t understand your screenshots :fearful:), but you could create a new View with your needed filters, grouping by Cliente or the field that you want.


#6

I think Alla’s request is very valuable.

How do you write a formula to compare (and give an error message if needed) a field’s input to a list of acceptable responses? Single/Multiple select doesn’t cut it when there are many acceptable responses (+500).

An example of this would be a field only accepting responses that are an action verb. I would load a list of the 500 most common verbs in a table, but how do I have a formula check the list and compare it to the field?


#7

How about a simpler example that led me to this request by Alla? I had just figured out how to do this in Google Sheets by the way. The example:

I have a master list of people I invite to house concerts, say 300 people. As they rsvp I get a new list of those that sent me an RSVP. I don’t want to spam the people the have already RSVP’d the next time I send a message to the master list, so what I want is the master list minus those that have already rsvp’d. That is called the intersection of two sets. If it helps, this is how it is specified in Google Sheets

=iferror(filter(MasterList!$B$2:$B, iserror(match(MasterList!$B$2:$B, B2:$B , false))), "no match")

Column B in both lists are just lists of email addresses.


#8

Great feature request. I think your request could be lumped with (or perhaps “improve the following”) feature request by @Jeremy_Oglesby:


#9

@Alla_Pob, @Matthew_Billiodeaux1, @Philip_Nelson,

Would @Alla_Pob’s request be satisfied by the ARRAY_DIFF() function I propose here?

I’m trying to make a unified feature request for more functions, and I want to make sure I have @Alla_Pob’s use-case covered. If ARRAY_DIFF() would not do it, what would do it?


#10

Hello Jeremy, although her function fits in perfectly with the feature request you are drafting, this would probably need to be a developed as yet another array function as her request (and mine) is for an intersect function:

Example:
ARRAY_INT([1, 2, 3, 4], [2, 4, 5], [0,2,4,9]) = [2, 4]


#11

Great, thank you - I’ll add it to the list.