Join Collect with collecting data from Multiple cells
Hi,
I have six "issue" columns and six "resolved" columns. I'm trying to collect all the Issue columns into a final column. I'm using the following formula but I'm getting #INCORRECT ARGUMENT SET
=JOIN(COLLECT([Issue I]@row, [Resolved I]@row, 0, [Issue II]@row, [Resolved II]@row, 0, [Issue III]@row, [Resolved III]@row, 0, [Issue IV]@row, [Resolved IV]@row, 0, [Issue V]@row, [Resolved V]@row, 0, [Issue VI]@row, [Resolved VI]@row, 0), CHAR(10))
Best Answer
-
Hi @Rivky Emert
The COLLECT function will only bring data back from the very first range specified, based on the criteria listed after it.
It looks like you want to bring all the Issue's into one cell, but only if the associated Resolved column is blank (or not checked), is that correct? If so, I would actually use multiple IF statements added together, versus JOIN(COLLECT.
Ex: =IF([Resolved I]@row = 0, [Issue I]@row, "")
Try something like this:
=IF([Resolved I]@row = 0, [Issue I]@row, "") + IF([Resolved II]@row = 0, CHAR(10) + [Issue II]@row, "") + IF([Resolved III]@row = 0, CHAR(10) + [Issue III]@row, "") + IF([Resolved IV]@row = 0, CHAR(10) + [Issue IV]@row) + IF([Resolved V]@row = 0, CHAR(10) + [Issue V]@row, "") + IF([Resolved VI]@row = 0, CHAR(10) + [Issue VI]@row, "")
Let me know if this works for you!
Cheers,
Genevieve
Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.
Answers
-
Hi @Rivky Emert
The COLLECT function will only bring data back from the very first range specified, based on the criteria listed after it.
It looks like you want to bring all the Issue's into one cell, but only if the associated Resolved column is blank (or not checked), is that correct? If so, I would actually use multiple IF statements added together, versus JOIN(COLLECT.
Ex: =IF([Resolved I]@row = 0, [Issue I]@row, "")
Try something like this:
=IF([Resolved I]@row = 0, [Issue I]@row, "") + IF([Resolved II]@row = 0, CHAR(10) + [Issue II]@row, "") + IF([Resolved III]@row = 0, CHAR(10) + [Issue III]@row, "") + IF([Resolved IV]@row = 0, CHAR(10) + [Issue IV]@row) + IF([Resolved V]@row = 0, CHAR(10) + [Issue V]@row, "") + IF([Resolved VI]@row = 0, CHAR(10) + [Issue VI]@row, "")
Let me know if this works for you!
Cheers,
Genevieve
Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.
-
Yes, that works! thanks.
-
Great! I'm glad I could help.
Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!