Help with =JOIN(DISTINCT(COLLECT formula

I am really hoping you may be able to help with pulling together a formula! Looking to use a =JOIN(DISTINCT(COLLECT formula to generate a string of distinct codes for the Distrb SKU Nbr column where the SKU codes are separated by a comma.

Based on criteria: no checkmark in the Available on TMC column, corresponding unique BSS# number in the BSS# column and the New Distrb Sku Nbr column where a Y is listed in the Campus FLG column.

Example Below:

Example:

Criteria:

Available on TMC (checkbox column) is unchecked

BSS# = 1000123

New Distrb Sku Nbr = 4737470

CAMPUS FLG = Y

Distrb Sku Nbr Skus Below:

4360190

4790400

6697882

6698021

9035601

9035775

4790370

6267314

String of Distrb Sku Nbr in the Campus Restrict column would display as: 4360190,4790400,6697882,6698021,9035601,9035775,4790370,6267314

Thank you and have a great day!

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

You have basically spelled out exactly what you need to do.

=JOIN(DISTINCT(COLLECT({range to join}, {1st criteria range}, 1st criteria, {2nd criteria range}, 2nd criteria, {3rd criteria range}, 3rd criteria)), ",")

JD_425

Hi Paul,

Thank you for much for your insight! I followed the path but don't think I have it quite right.

=JOIN(DISTINCT(COLLECT({Distrb Sku Nbr}, {Available on TMC}, “0”, {BSS #}, BSS #, {New Distrb Sku Nbr}, New Distrb Sku Nbr, {CAMPUS FLG}, “Y”))), ",")

Paul Newcome

I am guessing you are getting the #UNPARSEABLE error?

Check out your quotes. See how some are slanted and others are straight up and down? Those slanted ones are called "smart quotes" which (ironically enough) are not recognized as valid characters in Smartsheet. Try retyping them here in the Community, directly in Smartsheet, or in a text editor such as Notepad (not Word).

You also have one too many closing parenthesis there before the delimiter. You should only have two (one for the DISTINCT function and one for the COLLECT function). That third one is closing out the JOIN function, so the delimiter and following closing parenthesis are outside of syntax.

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.