Comparing two multiple value columns

[quentinleroux]

Hi everybody !

I would like to know how to add in column that features only the similar values found in columns A and B ?
(In my case the A and B columns are Lookup from two différents tables but I guess it works the same).

Thanks a lot for your help !

[bruceconsulting]

Hi, you may have to use the IF, AND and FIND functions together as such:

IF(AND(FIND("john",{A}),FIND("john",{B})),"john",IF(AND(FIND("paul",{A}),FIND("paul",{B})),"paul".....

Unfortunately, you would have to make this formula very long to take into consideration all possible combinations.  Views and/or automations may be a better approach.  What exactly are you trying to accomplish?

[quentinleroux]

Thanks for the answer.
Unfortunatly I do not know all the possible datas. I'm trying to keep a single array/list of user, out of 2 lookup fields.

> My goal is to find multiple singers who matches at least one location AND at least one type and put them into a cell (array)

I have a Table 1 : Musicians
- singer : John
- location : area13, area2, area4, area26 (multiple select)
- type : rock, pop, classic, jazz (multiple select)

- singer : Paul
- location : area13, area2, area4, area28
- type : electro, hardrock

- singer : George
- location : area13, area4, area28
- type : electro, jazz 

I have a Table 2 : Concerts
- concert : First Concert
- location : area13, area26
- type : pop, hardrock
- possible singers : John, Paul

In the previous exemple A are all the "location" matches and B all the "type" matches from link + lookups

[pressGO_design]

Because you're using multi-select fields, there’s no link between Table 1 and Table 2, so there’s no way that information in Table 1 can be looked up in Table 2. In order to create that kind of relationship, you would need to have tables for types and locations and musicians that you can link to in the concerts table and then use ARRAYUNIQUE() to find who can work where and who can sing what.

But to compare the two arrays to find the common denominators, you're going to need a script (or a monster formula).

[quentinleroux]

Thanks for the answer. Thats a good start 🙂

Unfortunetly when I do the same il will have for concert A : John, George, John like he is creating two arrays (assuming that John is on both Area 1 and 3, he will apear twice 🧐.)

Rollup : Concert Areas / Musicians

ARRAYUNIQUE(values)

Il will keep looking on the Script but I'm not familiar with the syntax yet.

[pressGO_design]

Yes. The script would first need to de-dupe both arrays and then compare the de-duped arrays to each other. I am terrible at scripting so am no help here. Perhaps posting in the scripting forum might be helpful?

Like @bruce1 said, you can use views and filters to get the information you want without using a script.