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

Find more posts tagged with

Accepted answers

Chris Mondeau

Hi @Pierre Mostert

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!

All comments

Chris Mondeau

Hi @Pierre Mostert

I hope I interpreted what you're looking for correctly:

Source Sheet

Destination Sheet

Let me know if this helps, thanks!

Pierre Mostert

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

Pierre Mostert

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

NikhilB

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.

Genevieve P.

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.