JOIN/COLLECT when referencing a Dropdown (Multi Select)

Katy Hall

I am trying to use a conditional IF based JOIN/COLLECT to generate a phrase in another column for notification purposes, but the column that the JOIN/COLLECT is referencing is a multi-select column and I can’t figure out a way to join multiple items from that column together.

The column of interest is a list of possible allergens that could be in a food product. Based on the number of allergens present the phrasing may change. For instance. If “wheat” is chosen as an allergen It would say “Contains: wheat”, If “wheat and milk (lactose)” are chosen as allergens it would say “Contains: wheat and lactose” and then a final option if more than two are chosen the final result would include commas in addition to the “and”. I am trying to avoid including the allergen selections in separate columns, but currently if I try to JOIN/COLLECT 2 or more selections on the multi drop down it repeats both selections. Example: “Contains: Milk (Lactose) Wheat and Milk (Lactose) Wheat”

Below are screenshots of the columns and formula currently being trialed.

Leibel S

@Katy Hall

The below should do the trick

=IF(COUNTM(Allergens@row) > 0, "Contains: " + SUBSTITUTE(SUBSTITUTE(Allergens@row, CHAR(10), " and ", COUNTM(Allergens@row) - 1), CHAR(10), ", "), "")

Mark Cronk

Hi @Katy Hall ,

You won't get the "and" but try:

=IF(ISBLANK([number of allergens]@row), " ", "Contains: " + JOIN(DISTINCT(COLLECT(allergens@row)),", ")

Any better?

Mark

Katy Hall

Thanks Mark, unfortunately that is returning an #invalid operation but will see if this leads me down a new path with this new perspective!

Leibel S

@Katy Hall

The below should do the trick

=IF(COUNTM(Allergens@row) > 0, "Contains: " + SUBSTITUTE(SUBSTITUTE(Allergens@row, CHAR(10), " and ", COUNTM(Allergens@row) - 1), CHAR(10), ", "), "")

Katy Hall

@Leibel S thank you so much! This worked perfectly!!

Mark Cronk

Well done @Leibel S !

Mark