Join Collect formula

Alicia L

I've got this formula to return the information I need except for one part.

This returns: comment1, comment2, comment3, comment4, comment5, comment6, comment7, comment8, comment9

What is missing is the maintenance person's name before each comment.

Where do I add the mechanic/electrician column within the formula?

I tried adding it to the end of the formula, but I get an incorrect argument set error.

Find more posts tagged with

Join

collect

combining two formulas

Accepted answers

Paul Newcome

The source sheet is the reference sheet. It wouldn't be a second JOIN/COLLECT. You would insert a column in the source/reference sheet that combines these two columns on every row. Something like

=[Mechanic/Electrician]@row + ": " + Summary@row

Then in the sheet where you have the JOIN/COLLECT, you would reference this new helper column instead.

All comments

Paul Newcome

You would need a helper column in the source sheet that puts the name before the comment on each row. Then you would JOIN/COLLECT this helper column.

Alicia L

Is the source sheet and reference sheet the same thing? If so, the screen shot in the question is the source sheet. Mechanic/Electrician is the column name. I named it {Maint Person} in the formula to shorten it.

Where do I add another join/collect in my current formula? I'm not sure if you can see the screenshots I put in, so I'll write them out here.

This is the formula that works to pull in the comments.

=IFERROR(JOIN(COLLECT({End of shift maintenance summary}, {EOS Maint Date}, Date@row, {EOS Maint Shift}, "1st"), ", "),"")

I tried this formula to pull in the maintenance person's name before their corresponding comment, but I get the incorrect argument error.

=IFERROR(JOIN(COLLECT({End of shift maintenance summary}, {EOS Maint Date}, Date@row , {EOS Maint Shift}, "1st"), {Maint Person}, ", "),"")

Paul Newcome

The source sheet is the reference sheet. It wouldn't be a second JOIN/COLLECT. You would insert a column in the source/reference sheet that combines these two columns on every row. Something like

=[Mechanic/Electrician]@row + ": " + Summary@row

Then in the sheet where you have the JOIN/COLLECT, you would reference this new helper column instead.

Alicia L

That worked perfect. Thank you!!

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.