Help

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

Array intersect formula

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

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?

13 Comments
Elias_Gomez_Sai
13 - Mars
13 - Mars

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:

Alla_Pob
5 - Automation Enthusiast
5 - Automation Enthusiast

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:

67e123a31d7433f772057827041b671751a8580f.png

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:

Screen Shot 2017-02-24 at 2.57.34 PM.png

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

Alla_Pob
5 - Automation Enthusiast
5 - Automation Enthusiast

Did this make the question clear?

Elias_Gomez_Sai
13 - Mars
13 - Mars

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.

Lux_Leadership
4 - Data Explorer
4 - Data Explorer

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?

Philip_Nelson
4 - Data Explorer
4 - Data Explorer

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.

Matthew_Billio1
7 - App Architect
7 - App Architect

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

Jeremy_Oglesby
14 - Jupiter
14 - Jupiter

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

Matthew_Billio1
7 - App Architect
7 - App Architect

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]

Jeremy_Oglesby
14 - Jupiter
14 - Jupiter

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