Add to the formula as shown below:
=JOIN(COLLECT({Column I Want Data}, {Column I Want Data},@cell<>"",{Reference Number}, [Reference Number]@row), ",")
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
Best Answer
-
Add to the formula as shown below:
=JOIN(COLLECT({Column I Want Data}, {Column I Want Data},@cell<>"",{Reference Number}, [Reference Number]@row), ",")
Answers
-
Add to the formula as shown below:
=JOIN(COLLECT({Column I Want Data}, {Column I Want Data},@cell<>"",{Reference Number}, [Reference Number]@row), ",")
-
Thank you so much, that worked perfectly! Solved my issue.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 433 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 506 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!