How to separate multi select values and collect distinct values?

Liam Burnett

Each period (P1,P2,P3 etc) we collect KPIs for our projects. In some new KPIs we have been asked to collect the answers involve multi select drop downs.

What we would like to be able to do is to each period have a list of only the unique values.

I have tried using a formula which uses JOIN, DISTINCT and COLLECT and it works but only when all of the multi select values are the same so it can still produce some duplicates.

Is it possible to get the formula to only return unique values for that period.

e.g:

Completion of low carbon assessment.

Hydrogen powered plant and equipment.

Low carbon concrete or other materials.

Solar solutions.

Other.

I hope this makes sense.

thanks

Liam

Paul Newcome

Use a JOIN/COLLECT combo with a CHAR(10) delimiter and put the formula into a cell within a multi-select column.

Paul Newcome

Use a JOIN/COLLECT combo with a CHAR(10) delimiter and put the formula into a cell within a multi-select column.

Liam Burnett

Hi Paul,

thanks for your reply

This still gives a duplicate (e.g. 2x Hydrogen powered plant and equipment).

We are looking for the distinct multi select values so we have a list of topics covered in a period.

thanks

Liam

Paul Newcome

The formula needs to also be in a multi-select type column.

Liam Burnett

Thanks, I thought it was but it wasn't, works now :) 😂

Paul Newcome

Happy to help. 👍️