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 at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!