MAX and COLLECT to find the latest date with specific criteria?

Van Keuren, Jeremy

I have two sheets. The first sheet (called "Directory") lists the names of the organizations we work with (in a column called "Organization"). Another column (called "Date Last Journal") is supposed to display the date that is the latest date an employee interacted with an organization.

The second sheet (called "Complete") is the dump site for survey data that is completed by employees when they interact with one of the organizations in the Directory. An employee might interact (i.e. complete a survey) for an organization several times in a month. Each time a survey is completed, it is time stamped and the time stamp is placed in a column called "Created".

For each organization listed on the Directory sheet, I need Smartsheet to read the "Created" column on the "Complete" sheet, match the name of the organization, ascertain the latest date associated with that name in the "Created" column, and display it in the "Date Last Journal" field associated with the organization.

I haven't been able to figure out how yet. I think it has something to do with nesting COLLECT inside MAX, but my ideas don't parse. For example, =MAX(COLLECT({Stage 3: Complete Range 1}, {Stage 3: Complete Range 2}, "Bob's Chicken Farm"), 1)), where Bob's Chicken Farm is the name of an organization.

Do you have suggests to help with this?

Genevieve P.

Hi @Van Keuren, Jeremy

You are very close! Yes, a MAX(COLLECT would be an ideal formula for this. However, since this is not a part of an INDEX(MATCH or any other formula, you don't need the 1 or the extra closing parentheses at the end.

Try this:

=MAX(COLLECT({Stage 3: Complete Range 1}, {Stage 3: Complete Range 2}, "Bob's Chicken Farm"))

You only need 2 closing Parentheses (one for the MAX, one for the COLLECT). Let me know if this works for you!

Keep in mind that if your Created Date column is a Date Type of column, this formula will also need to be placed within a Date Type of column in the destination sheet, as well.

If you're receiving an error, let us know what it is so we can help troubleshoot further.

Cheers!

Genevieve 

Genevieve P.

Hi @Van Keuren, Jeremy

You are very close! Yes, a MAX(COLLECT would be an ideal formula for this. However, since this is not a part of an INDEX(MATCH or any other formula, you don't need the 1 or the extra closing parentheses at the end.

Try this:

=MAX(COLLECT({Stage 3: Complete Range 1}, {Stage 3: Complete Range 2}, "Bob's Chicken Farm"))

You only need 2 closing Parentheses (one for the MAX, one for the COLLECT). Let me know if this works for you!

Keep in mind that if your Created Date column is a Date Type of column, this formula will also need to be placed within a Date Type of column in the destination sheet, as well.

If you're receiving an error, let us know what it is so we can help troubleshoot further.

Cheers!

Genevieve 

Van Keuren, Jeremy

Ooops, yep, that there did it. I also had my ranges switched around. Thanks!

Genevieve P.

No problem! Glad you were able to get it working 🙂

jcouncil

I am trying to accomplish something similar to this.

I have a sheet that the Account name column, status of customer in "Customer 12 Month Outlook" Column, and a Last Modified date column. I want to then pull the "Customer 12 month outlook" for each Account into another spreadsheet. Every quarter The "Customer 12 month outlook" is entered as a new row, so there may be multiple rows for one account to search for latest modified by date and pull back the associated customer 12 month outlook into.

would a MAX(COLLECT()) work here as well?

The most recent 12month outlook (based on last modified date) from the above sheet, should populate in the "latest 12 month customer outlook" in the sheet here based on the associated Account Name

Genevieve P.

Hi @jcouncil

Yes! I would use both MAX and COLLECT functions here, however you would want these in an INDEX as well, since you're pulling back text and not the date (you're using the date as a criteria).

For example:

=INDEX(COLLECT({Column to return}, {Criteria Column}, "Criteria 1", {Date Column}, "Date"), 1)

However, since we're looking for the MAX date but from a specific criteria, we'll need to us a MAX(COLLECT inside of this formula as well:

=INDEX(COLLECT({Column to return}, {Criteria Column}, "Criteria 1", {Date Column}, MAX(COLLECT({Date Column}, {Criteria Column}, "Criteria 1"))), 1)

So in your case, something like this:

=INDEX(COLLECT({Customer 12 Month Column}, {Account Column}, [Account Name]@row, MAX(COLLECT({Date Column}, {Account Column}, [Account Name]@row))), 1)

See: Formula combinations for cross sheet references

Let me know if this works for you!

Cheers,

Genevieve

jcouncil

Hi @Genevieve P. thanks for the quick response!

I tried your suggestion but am receiving an error: #INCORRECT ARGUMENT SET

=INDEX(COLLECT({SFA_SFDC_Scorecard Range 2_12 month outlook}, {SFA_SFDC_Scorecard Range 4_Account}, [Account Name]@row, MAX(COLLECT({SFA_SFDC_Scorecard Range 1 (Last Modified)}, {SFA_SFDC_Scorecard Range 4_Account}, [Account Name]@row))), 1)

Genevieve P.

Hi @jcouncil

My apologies! I missed adding the range you're looking into before the MAX(COLLECT.

=INDEX(COLLECT({SFA_SFDC_Scorecard Range 2_12 month outlook}, {SFA_SFDC_Scorecard Range 4_Account}, [Account Name]@row, {SFA_SFDC_Scorecard Range 1 (Last Modified)}, MAX(COLLECT({SFA_SFDC_Scorecard Range 1 (Last Modified)}, {SFA_SFDC_Scorecard Range 4_Account}, [Account Name]@row))), 1)

jcouncil

@Genevieve P. that did the trick! Thanks so much :)

jcouncil

@Genevieve P. - wondering if you can help me solve for another max collect formula use:

I am tracking the latest date a customer was approved for use as a reference.

I have a Reference Request Tracker sheet that records 1 row for each opportunity, and may have up to 5 customers entered as reference options. (Those 5 customer names are entered into 5 separate columns (ie. Cust Ref #1, Cust Ref #2, etc.)

I want to pull the final approved on date for each of the Customers when the Final Request status for the entire row is "approved".

=MAX(COLLECT({[ENT] Reference Request Tracker Range 2}, {Reference #1}, [Account Name]@row, {Reference Request Range 2}, "Approved"), 1)

Where...

Reference Request tracker Range 2 = Final request approved on Date,

Reference #1 = column "Reference #1"

Reference Request Range 2 = Final Request Status

This formula is resulting in either #Date Expected or #Invalid Data Type.

Can you help me identify the issue? Thanks!

Genevieve P.

Hi @jcouncil

It looks like the syntax is correct, which is great!

What type of column are you putting the formula into? Since it's returning a Date, you'll want to write the formula into a Date type of column.

Let me know if that fixed the error! 🙂

jcouncil

@Genevieve P. Unfortunately, it was already a date type column, so that didn't resolve.

Genevieve P.

Hi @jcouncil

My apologies, I missed that you had a 1 at the end of your formula:

=MAX(COLLECT({[ENT] Reference Request Tracker Range 2}, {Reference #1}, [Account Name]@row, {Reference Request Range 2}, "Approved"), 1)

This means that your formula is looking for the MAX between either a Date or a 1, which will give you an error. Either you'll need to replace the 1 with a Date value, or remove it all together:

=MAX(COLLECT({[ENT] Reference Request Tracker Range 2}, {Reference #1}, [Account Name]@row, {Reference Request Range 2}, "Approved"), DATE(2022, 01, 01))

or

=MAX(COLLECT({[ENT] Reference Request Tracker Range 2}, {Reference #1}, [Account Name]@row, {Reference Request Range 2}, "Approved"))

Cheers,

Genevieve

jcouncil

@Genevieve P. That did the trick!

So, this solves for any customer approved for use as a reference #1. I need to apply the same logic for reference #2-5, and then have a summary of all 5 references to show the latest approved date for that one particular customer regardless of which reference # they were used as. Do you have a suggestion on best practice to accomplish this? I was assuming with helper cells (which is essentially what the formula you've helped me with here was starting) for each reference field and then a max collect across those values for each customer.

Thanks for all the help, you are incredible!!

Genevieve P.

Glad I could help, @jcouncil! 🙂

Yes, I agree with that structure - once you've brought back the content you need into this sheet you could use a MAX(COLLECT across these columns well.

I would actually suggest that it may be best to chat through this over a screen share and discuss the best way to reach your end goal; I believe your account may be eligible to book Pro Desk sessions! These are 30-minute screen share sessions where you can discuss best practices, see:

https://help.smartsheet.com/articles/2478411-get-coaching-pro-desk-info

Cheers,

Genevieve

Steve Grinder

Trying to apply this great logic to a simple problem I am having. For simplicity I'll use:

Column A for a "Task Name"

Column B for last date updated

Column C for % Complete.

Looking to show in a report the last Task completed for a particular project. These are multiple projects all on the sheet, and the tasks are nested under each one.

So - I'm looking to reference this sheet and pull out the Task name in a given range (for a particular project), that was the latest line posted at 100% complete.

Thanks