Join Collect with Multiple Criteria
I have been searching the community posts for a solution, but have not been able to find one that works. I have a sheet that I need to combine multiple columns into one helper column. Here is the description of what I am trying to do (screenshot included below for reference)
If a date is less than 30 days from today, add the task name and the date to the helper column. The current formula I am using is: =JOIN(COLLECT(Task@row, June@row, <TODAY(30)))
However, it is only adding the task name to the helper column, not the date as well.
I actually need a formula that will look across the columns July through June and add the task and date if it is less than 30 days out.
Best Answer
-
If you are willing to add another helper column, you can do it this way.😀
Helper Column 1
=JOIN(COLLECT(July@row:June@row, July@row:June@row, ISDATE(@cell), July@row:June@row, ABS(@cell - TODAY()) < 30))
Helper Column 2
=IF(ISTEXT([Helper Column 1]@row), Task@row + " " + [Helper Column 1]@row)
Answers
-
Pulling the date from a date formatted column into a text column makes things a little more complicated. I have this working in a test sheet. I am assuming some of the dates in your image are for testing purposes? In the event there are multiple meetings of the same type (though it doesn't look like that could happen), this solution will display the date for the earliest one.
=IF(COUNTIFS([July]@row:[June]@row, @cell >= TODAY(), [July]@row:[June]@row, @cell <= TODAY(30)) = 1, MONTH(MIN(COLLECT([July]@row:[June]@row, [July]@row:[June]@row, @cell >= TODAY(), [July]@row:[June]@row, @cell <= TODAY(30)))) + "/" + DAY(MIN(COLLECT([July]@row:[June]@row, [July]@row:[June]@row, @cell >= TODAY(), [July]@row:[June]@row, @cell <= TODAY(30)))) + "/" + YEAR(MIN(COLLECT([July]@row:[June]@row, [July]@row:[June]@row, @cell >= TODAY(), [July]@row:[June]@row, @cell <= TODAY(30)))) + " " + [Task]@row, "")
-
Thanks for your work on this! However, I am not able to get it to work. When I copy and paste the formula into my sheet, the cell returns blank.
-
If you are willing to add another helper column, you can do it this way.😀
Helper Column 1
=JOIN(COLLECT(July@row:June@row, July@row:June@row, ISDATE(@cell), July@row:June@row, ABS(@cell - TODAY()) < 30))
Helper Column 2
=IF(ISTEXT([Helper Column 1]@row), Task@row + " " + [Helper Column 1]@row)
-
Thank you, jmyzk_cloudsmart_jp!
The first formula for Helper Column 1 works beautifully. The formula for Helper Column 2 did not work for me, but you got me in the right direction! I used the formula below in Helper Column 2 to join the two columns I needed together. I had to make sure that Helper Column 1 was a Date column.
=JOIN((Task@row + " " + [Helper Column]@row))
THANK YOU SO MUCH!!!
-
Happy to help.😁
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!