Collect values in multi-select field from different lookup sheet
Hi,
I have two sheets. The first sheet (destination sheet) has the multi-select column 'Value', and the second the values I want to collect based on Project ID (unique identifier) between sheets. The unique identifier could appear more than once, so incorporated the Row ID in lookup sheet if required.
I don't think the helper row is required but just added it.
The result I am looking for is depicted in the 1st screenshot below.
Lookup/source sheet:
Some assistance will be appreciated.
Regards,
Pierre
Best Answer
-
I hope I interpreted what you're looking for correctly:
Source Sheet
Destination Sheet
I'm using the collect function to gather all project values that match the corresponding project ID. I also added the distinct function to only return the unique project values (if you want all of them including duplicates you can remove the distinct portion). From there I used the join function alongside the unichar with the alt code 10 which is the [line break] character needed to split out multi-select values.
Let me know if this helps, thanks!
Answers
-
I hope I interpreted what you're looking for correctly:
Source Sheet
Destination Sheet
I'm using the collect function to gather all project values that match the corresponding project ID. I also added the distinct function to only return the unique project values (if you want all of them including duplicates you can remove the distinct portion). From there I used the join function alongside the unichar with the alt code 10 which is the [line break] character needed to split out multi-select values.
Let me know if this helps, thanks!
-
Hi Chris
Thanks it works great. Much appreciated.
I just have one more question.
How do I adapt the formula to only collect the values IF the flag is TRUE in the source sheet? See modified screenshot below:
Regards,
Pierre
-
Hi Chris
No worries, I figured it out. I see the COLLECT already acts as an IF statement. I just had to add the second condition to include TRUE.
Thanks again!
Pierre
-
Hello. I am not sure if any of you will get a notification when I add this comment but just going to give it a shot. I am actually trying to achieve the exact opposite of this. The source sheet actually has a multi select dropdown and I want to seperate it into multiple rows into the destination sheet. Any ideas? Thanks in advance.
-
Hi @NikhilB
There currently isn't a way to parse out the data from a multi-select cell into individual, separate values each in their own row. Please let the Product team know that this is a feature you would find useful by filling in this form, here.
Thanks!
Genevieve
Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!