Need help removing blanks from a join collect formula

Hi,

I'm trying to use a join collect formula that referencing a column in a helper sheet.

=JOIN(COLLECT({Column I Want Data}, {Reference Number}, [Reference Number]@row), ",")

It's pulling everything from {Column I Want Data}, that matches the [Reference Number] with {Reference Number} on the helper sheet and any cell that is blank will return these commas" , , , , , , , , , , , ," how can I edit my formula to ignore the blanks that are in {Column I Want Data}

1st image is the one with my formula, you can see the commas, where a cell is blank

2nd image is how the data looks in my helper sheet

Find more posts tagged with

Formulas

Join

collect

Accepted answers

Leibel S

@N_K

Add to the formula as shown below:

=JOIN(COLLECT({Column I Want Data}, {Column I Want Data},@cell<>"",{Reference Number}, [Reference Number]@row), ",")

All comments

Leibel S

@N_K

Add to the formula as shown below:

=JOIN(COLLECT({Column I Want Data}, {Column I Want Data},@cell<>"",{Reference Number}, [Reference Number]@row), ",")

N_K

https://community.smartsheet.com/discussion/comment/394444#Comment_394444

Thank you so much, that worked perfectly! Solved my issue.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.