Help

Comparing two multiple value columns

Topic Labels: Formulas
2091 5
cancel
Showing results for 
Search instead for 
Did you mean: 
quentinleroux
5 - Automation Enthusiast
5 - Automation Enthusiast

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

Capture d’écran 2023-01-24 à 20.48.28.png
Thanks a lot for your help !

5 Replies 5
bruceconsulting
7 - App Architect
7 - App Architect

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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 : pophardrock
- 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
10 - Mercury
10 - Mercury

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.

Screenshot 2023-01-24 at 5.59.34 PM.png

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

quentinleroux
5 - Automation Enthusiast
5 - Automation Enthusiast

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
10 - Mercury
10 - Mercury

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.