Jan 24, 2023 11:54 AM
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 !
Jan 24, 2023 01:23 PM
Hi, you may have to use the IF, AND and FIND functions together as such:
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?
Jan 24, 2023 02:10 PM - edited Jan 24, 2023 02:22 PM
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
Jan 24, 2023 03:04 PM - edited Jan 24, 2023 03:11 PM
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).
Jan 25, 2023 12:54 AM
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
Il will keep looking on the Script but I'm not familiar with the syntax yet.
Jan 25, 2023 07:46 AM - edited Jan 25, 2023 08:08 AM
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.