Need assistance with collecting and then joining

Rebecca Panaccione

Hello,

I have a test master grid sheet where dates of leave (and different types of leave) are collected through a form.

Formula used for collecting dates together is:

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

I have a 2nd sheet from which I want to collect information from the master but for specific dates. For instance, only collect the dates together if the Start Date is between 6/1/20 and 6/30/20. I assume this is a combination of collect and join, but I have no idea which order to nest.

This is the formula I've determined so far, but now need to add the piece that collects for the specified dates. This cannot be a report because I will automate approvals on the data. I will substitute "June...) with the column-specific name. Screenshot of destination sheet below.

=JOIN(COLLECT({June Dates Together}, {June Email}, [Email/Contact]@row, {June A/L}, >1), " , ")

See Rebecca Panaccione on the bottom row. The above formula works to collect and combine all the dates from the Master as specific to Annual leave. That part works. I would like to fine tune this to collect the information for only the dates that start with 6/1/2020. Carry over is okay. It's the start date that drives the data.

As always, thank you so much for your time. You make me look smart!

Rebecca Panaccione

Genevieve P.

Hi Rebecca,

Well-done! This formula looks great. It sounds like you just need to add in another range (Start Date column) and criteria (on or after June 1st).

Try this:

=JOIN(COLLECT({June Dates Together}, {June Email}, [Email/Contact]@row, {June A/L}, >=1, {Start Date}, >= DATE(2020, 6, 1)), " , ")

(Note: I also added in an = sign before your 1, just in case someone only took one hour). Here I used the DATE function to specify the specific date to search for in the Start Date column. Does that make sense?

Let me know if this works for you, or if you have any questions!

Cheers,

Genevieve

Genevieve P.

Hi Rebecca,

Well-done! This formula looks great. It sounds like you just need to add in another range (Start Date column) and criteria (on or after June 1st).

Try this:

=JOIN(COLLECT({June Dates Together}, {June Email}, [Email/Contact]@row, {June A/L}, >=1, {Start Date}, >= DATE(2020, 6, 1)), " , ")

(Note: I also added in an = sign before your 1, just in case someone only took one hour). Here I used the DATE function to specify the specific date to search for in the Start Date column. Does that make sense?

Let me know if this works for you, or if you have any questions!

Cheers,

Genevieve

Rebecca Panaccione

Genevieve,

Thank you so much!

~Rebecca

Genevieve P.

No problem! Let me know if you have any questions about it 🙂

Sarah Underwood

@Genevieve P

I am trying to create a Join/Collect function however keep running into #No Match or #Incorrect argument. I am trying to join all the state abbreviations based on if the Branch NMLS# matches the Branch Roster NMLS#. I tried copy & pasting what you posted above and make it my own but keep running into issues.

Here is the current code I have tried:

=JOIN(COLLECT([State Abbr]:[State Abbr], [Branch NMLS#]:[Branch NMLS#] = [Branch Roster Branch NMLS]@row, [Branch Roster Branch NMLS]@row), " , ")

With a result of #No Match.

Please advise.

Genevieve P.

Hi @Sarah Underwood ,

Since you have the criteria built into the sheet, you don't need to list it twice in your formula. Try this:

=JOIN(COLLECT([State Abbr]:[State Abbr], [Branch NMLS#]:[Branch NMLS#], [Branch Roster Branch NMLS]@row), " , ")

If this doesn't work, can I ask how your Branch NMLS column is being populated? It looks like the numbers might be recognized as text instead of numbers. You can either turn your Branch Roster Branch NMLS column into a text string by adding a ' in front of the numbers (ex. '1071) or you can adjust the formula in your Branch NMLS column to use the VALUE function and return a number.

Let me know if this works for you!

Cheers,

Genevieve

Sarah Underwood

@Genevieve P ,

Thank you for your response. I fixed the initial spreadsheet to ensure the data was coming over as numbers vs text, re-uploaded, and updated the formula to no avail. It is coming up #No Match.

Genevieve P.

Hi @Sarah Underwood

Your formula is written correctly, which is great! However #No Match indicates that it can't find the [Branch Roster NMLS] value in your range, so there's something blocking the formula from reading your values as the same type.

What happened when you adding a ' in front of all the numbers that are criteria in your formula? For example:

Branch Roster NMLS column

'1071

'1717892

etc.

Another test you can do, to make sure your numbers are numbers, is to put this formula in a helper column:

=IF(ISNUMBER([Branch NMLS#]@row), "Yes", "No")

This will return a "Yes" if the number in your primary column is being recognized as a number.

Since the formula should work, it would be helpful to know how many of your values are returning #No Match. Can you try with the other values in the list and see if any of them work?

If none of this has helped, how are you populating these two Branch columns, are you using a formula to create the numbers or are they manually typed in through a form or onto the sheet? I want to try and replicate the error you're receiving.

Thanks!

Genevieve