JOIN COLLECT formula

Rebecca Panaccione

Hello,

I could use assistance building a JOIN COLLECT formula, I think. Here is what I'm trying to do.

Using the formula =JOIN(COLLECT(rangecriterion_range1criterion1[criterion_range2​criterion2​...]) , I don't understand what I'm collecting and what I'm joining.

p.s. I already have the -SUMIFS in place to do the math from source to destination.

Here is a screen shot of my source sheet

and my destination sheet

As you can see I'm trying to combine set of leave date, Start Date - End Date, Start Date, etc in each of the appropriate columns (A/L, S/L, M/L). Extra criterion is that if the start date and end date are the same, just enter the start date

Can this even be done?

Thank you for any and all help!

Rebecca

Genevieve P.

Hi @Rebecca Panaccione, (and nice to hear from you again!)

You can use a JOIN(COLLECT formula to bring together the information from multiple cells in one sheet to another.

However, before bringing over the dates from your source sheet, you will want to list out the full date range in one cell in the source sheet (such as 1/31/20 - 2/4/20). To do so, you can set up a helper column in your source sheet to just JOIN this information, first:

=JOIN([Start Date]@row:[End Date]@row, " - ")

This will bring your date range together with the " - " symbol, as you have it formatted in your current demo.Then you can create your JOIN(COLLECT with cross-sheet references, referencing this helper column:

=JOIN(COLLECT({Total Date Range in Source Sheet}, {Email/Contact in Source Sheet}, [Email/Contact]@row, {Total A/L in Source Sheet}, >=1), " , ")

You will notice that I built in two different criteria. The formula should read as follows:

• Join together the content of the cells in the column "Dates Together"
• IF
• The Contact in the source sheet matches the contact of my current sheet, in this row
• The Total A/L column in the source sheet has a count greater than or equal to 1
• (this way it won't add in any of your S/L or M/L dates)

Here are some Help Center articles that I used to create this formula:

• JOIN function
• COLLECT function
• Hide Column (for your Helper column)
• Cross-Sheet references

Let me know if you have any questions about this!

Cheers,

Genevieve

Rebecca Panaccione

Yikes!

Genevieve,

Thank you so much for this in-depth answer. I'm going to work it today and will reply with any additional questions.

Rebecca

Rebecca Panaccione

Genevieve,

Your instructions worked perfectly. This is my result.

I've tried a couple of different ways to make the "Dates Together" work better, but haven't figured it out. Is there a way to quantify that IF the end date is = or < than the start date it will default to the start date? For example, the first row shows dates of 01/03/20 - 01/03/20, 01/17/20 - 01/17/20. It reads awkwardly unless the dates are more than one. screenshot below. Just need 01/03/20, 01/17, 20.

I tried creating a column in the source sheet (to possibly change the format of the form) to join base dates (1/3, 1/17) and then join with delimiter of ", 2020 " or " 2020 " but it just ads 2020 between the dates. Not pretty. :(

Any suggestions?

Thank you again!

Rebecca

Genevieve P.

Hi Rebecca,

I'm so glad to hear it works for you!

Yes, there is a way to add this IF statement in: it will need to be done in your initial "Dates Together" column in the source sheet.

Instead of having just =JOIN([Start Date]@row:[End Date]@row, " - "), try this:

=IF([End Date]@row <= [Start Date]@row, [Start Date]@row + "", JOIN([Start Date]@row:[End Date]@row, " - "))

This says IF the End Date is the same as the Start Date or before it, return the Start Date + blank (indicated by ""). Otherwise, if the dates are different, Join them together. You will need to add in the "" to ensure that the formula returns the start date as text in this column (instead of as a date type of entry, which would give you an error).

As another tip, you may want to use the "wrap text" function in your Date of Annual Leave column so that it doesn't have to be so long. Click on the title of the column to highlight the whole thing, then choose the paragraph option with the blue arrow in the top tool bar:

Let me know if there's anything else we can help you with!

Cheers,

Genevieve