JOIN(COLLECT

Kelly W

Hi,

I'm trying to use the join collect formula (example of my formula currently)

=JOIN(COLLECT({Meeting Minutes Topic & AI}, {Meeting Minutes Pursuit #}, [Pursuit #]@row, {Meeting Minutes Updated Date}, <TODAY-7, "-")

Objective: I want the formula to search the meeting minutes sheet (pic 2) to match the pursuit # column and return the Topic column & the Action item column into the highlighted cell below (pic one) only if the pursuit number matches AND the Updated date (on pic 2) is within the last 7 days.

Thank you so much for any help on this one!

Hollie205

You have a couple of options depending on how you want your data to show.

=JOIN(COLLECT({Topic}, {Pursuit #}, [Pursuit #]@row), "-") + "-" + JOIN(COLLECT({Action Item}, {Pursuit #}, [Pursuit #]@row), "-")

will look like this Topic 1-Topic 3-Action 1-Action 3

Or you can create a helper column on your reference sheet to go ahead and join the Action Item and the Topic Then use the below formula on your destination sheet.

=JOIN(COLLECT({Join Topic/Action}, {Pursuit #}, [Pursuit #]@row), "-")

and the output will look like this

Topic 1-Action 1-Topic 3-Action 3