Formula Help

I currently have a formula to Join and Collect Date from multiple rows in Smartsheet but it is displaying the data multiple times in the single cell, I need the formula to only show each unique ID once but can't seem to get the results to work. Every time, I adjust the formula below, to try and incorporate a new function to show each ID once, it returns various errors.

Current Formula: =JOIN(COLLECT([Transfer ID]:[Transfer ID], [Transfer Month and Year (Flow)]:[Transfer Month and Year (Flow)], [Transfer Month and Year (Flow)]@row), ", ")

Ex: 0000-MRCxxx (23 rows), 0000-CITxxx (1 row), and 0000-PHLxxx (3 rows) but I need the formula to return each ID 1 time each in a single cell)

Does anyone know how I can get the above formula to display each ID once in a single cell? The below screenshot is how it is currently displaying the IDs in a single cell.

Find more posts tagged with

Accepted answers

Paul Newcome

Wrap the entire COLLECT function in a DISTINCT function.

=JOIN(DISTINCT(COLLECT(…………………………………..)), ", ")

All comments

Paul Newcome

Wrap the entire COLLECT function in a DISTINCT function.

=JOIN(DISTINCT(COLLECT(…………………………………..)), ", ")

Shanna R

That worked. Thank you so much for the feedback.

Paul Newcome

Happy to help. 👍️

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.