Join Collect with Multiple Criteria

Paula Reynolds

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.

jmyzk_cloudsmart_jp

Hi @Paula Reynolds

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)

https://app.smartsheet.com/b/publish?EQBCT=15a2ac5d13384368b779349d51609720

Carson Penticuff

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, "")

Paula Reynolds

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.

jmyzk_cloudsmart_jp

Hi @Paula Reynolds

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)

https://app.smartsheet.com/b/publish?EQBCT=15a2ac5d13384368b779349d51609720

Paula Reynolds

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!!!

jmyzk_cloudsmart_jp

@Paula Reynolds

Happy to help.😁