Join/Collect/Distinct

I need some help with a Join/Collect/Distinct formula.

I used what is here: https://community.smartsheet.com/discussion/63326/join-collect and it got my 90% of the way I need to be. (Specifically this formula: =JOIN(DISTINCT(COLLECT({Timekeeper}, {Matter Number}, [Matter Number]@row)), "delimiter")).

My problem is that my "criterion" includes multiple values and I want the formula to assess each value and provide the corresponding state.

This is the formula with my info: =JOIN(DISTINCT(COLLECT({State1}, {Station1}, [Station(s)]@row)), ", ")

And this is what my main sheet looks like:

On the reference sheet each station is its own row:

How do I pull in the state for each station, specifically where there is more than one station (value) in one cell?

Find more posts tagged with

Accepted answers

L_123

=join(distinct(collect({State1},{Station1},find(@cell,Station@row)>0)),", ")

I would do something like that. You could also use a contains or has instead of find

All comments

L_123

=join(distinct(collect({State1},{Station1},find(@cell,Station@row)>0)),", ")

I would do something like that. You could also use a contains or has instead of find

Jennifer Lenander

The elusive "FIND" - oh, thank you!

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.