Help removing blanks from a join collect formula

Answers
-
Hi @AnneSh
1)This formula
=JOIN(COLLECT([SAT A]@row:[SAT 1]@row), ",")
Needs a criterion range and criteria in the COLLECT function
Like this
=JOIN(COLLECT([SAT A]@row:[SAT 1]@row, criterion range, criteria), ",")
2)This formula
=JOIN(COLLECT([SAT A]@row:[SAT 1], @cell<>"", [SAT A]@row:[SAT ID]@row), ",")
Is missing
@row after [SAT 1]
And although it does have the right number of elements they aren't in the right order. We'll come back to it.
3)This formula
=JOIN(COLLECT([SAT A]@row:[SAT 1]@row, @cell <> "", [SAT A]@row:[SAT 1]@row, ","))
Has the missing @row but the same sequence issues as the second one, and also has the closing parenthesis for the COLLECT after the delimiter for the JOIN.
I think formula 2 is the closest to what you want to achieve. If we add the @row and swap the last two components around so the criteria range is before the criteria, we have this:
=JOIN(COLLECT([SAT A]@row:[SAT 1]@row, [SAT A]@row:[SAT 1]@row, @cell <> ""), ",")
This will output the values of the cells between SAT A and SAT 1 in the current row, where those cells are not blank. I hope that is what you are looking for.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.2K Get Help
- 430 Global Discussions
- 152 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 74 Community Job Board
- 501 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 306 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!