=JOIN formula with two COLLECT statements
Hey Community,
I'm running into a roadblock with a formula that I thought was correct, but its coming up with the error #INVALID DATA TYPE.
Here is the formula I'm using in reference to the pictured cells attached:
=JOIN(AND(COLLECT([TOPIC2]:[TOPIC2], [TITLE2]:[TITLE2], TITLE@row), COLLECT([TOPIC2]:[TOPIC2], [DATE2]:[DATE2], DATE@row)), CHAR(10))
Essentially, here's what I want:
IF the team name in the [TITLE2] column equals the team name in the [TITLE]@row cell
AND the date in the [DATE2] column equals the date in the [DATE]@row cell
COLLECT all values in the [TOPIC2] column that meet this above criteria and put all those values in the [TOPIC]@row cell
Thank you for any help!!
Best Answer
-
See below formula. This will collect all TOPIC2 where TITLE2 matches TITLE@row and DATE2 matches DATE@row.
=JOIN(COLLECT([TOPIC2]:[TOPIC2], [TITLE2]:[TITLE2], TITLE@row,[DATE2]:[DATE2], DATE@row), CHAR(10))
The collect formula you can just keep on adding all the criteria that you want. No need for an AND statement.
Answers
-
See below formula. This will collect all TOPIC2 where TITLE2 matches TITLE@row and DATE2 matches DATE@row.
=JOIN(COLLECT([TOPIC2]:[TOPIC2], [TITLE2]:[TITLE2], TITLE@row,[DATE2]:[DATE2], DATE@row), CHAR(10))
The collect formula you can just keep on adding all the criteria that you want. No need for an AND statement.
-
@Leibel S - this worked perfectly. Thank you so much!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.8K Get Help
- 474 Global Discussions
- 202 Use Cases
- 515 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 82 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!