Collect values in multi-select field from different lookup sheet

Pierre Mostert

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

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!

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!

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