Join(Collect question

Hello,

I am trying to join all notes in a cell from another sheet if the phone number on that row matches the phone number on my current sheet's row. I can do this in google sheets but struggling to translate to smartsheets.

=JOIN(COLLECT({Notes}, {Phone #}, [Phone #]@row), ", ")

This is returning 9 commas for each cell. I've double checked references and formats.

Thanks in advance!

Find more posts tagged with

Join

collect

Accepted answers

All comments

Jason Tarpinian

Are there blank "Notes" associated with the phone number in your lookup range? Update the formula to exclude blanks and see if it works:

=JOIN(COLLECT({Notes}, {Notes}, NOT(ISBLANK(@cell)), {Phone #}, [Phone #]@row), ", ")

trcavine

https://community.smartsheet.com/discussion/comment/453572#Comment_453572

Thank you! That did take care of the comma issue, but it is still not returning the actual notes. The whole column is blank.

Jason Tarpinian

Are you able to share a screenshot of what your source sheet looks like? I'm wondering if it's just a small formatting issue or something.

Another thing you can verify is that the cross sheet ranges are correct. Sometimes if you select an entire column, the pop-up window will refresh without you realizing it and re-selects just the first row cell - I've had many frustrating times with formulas because of this happening.

trcavine

https://community.smartsheet.com/discussion/comment/453590#Comment_453590

I can't post client's phone numbers but I can say the vlookup formula works just fine using the same two columns as the reference. Double checked the references and the whole column is selected.

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.