Formula returning a value for the highest date and for a specific name

eohlsson@optronicsinc.com

Hi all!

I need help with a formula in a sheet summary field: I want to return a performance grade value from Column A assigned to a particular name in Column B and for the highest/most recent date in Column C.

In this scenario, a list of names will be entered in column B once per month, a performance grade will be entered in Column A for each name, and the date of entry will be manually entered in Column C. All columns are located in the same sheet.

I have tried an =INDEX(MATCH(MAX formula with a conditional element without any success. Would be very grateful for any support!

Best,

Erik Ohlsson

prime_nathaniel

Hello,

I assume this is what you are looking for, made a couple assumptions about your use case but the general approach is easily adaptable. By the looks of it you are probably doing on going certifications or some kind of quality testing.

A = grades as int's, would work as percents or anything sortable
B = palceholder for names [A, B] could be contact card or dropdown doesn't matter so long as its a key
C = date of entry, my version assumes you can have multiple entries on the same day of the same or of different grade values. It you have unique entries per day solution works and this feature would become an safety feature

Max Date:

=MAX(COLLECT(C:C, B:B, B@row))

Value from Max Date for that user:

=MAX((COLLECT(A:A, B:B, B@row, C:C, [Max Date]@row)))

Combined into one formula:

=MAX(COLLECT(A:A, B:B, B@row, C:C, MAX(COLLECT(C:C, B:B, B@row))))

Approach explained:

We want the max date of the list of entries for that user, there could be one entry there could be 1000 entries we only care what the most recent date is as per your requirement "for the highest/most recent date in Column C".
So we use collection of dates, where user = the user of the current row which is MAX(COLLECT(C:C, B:B, B@row)). Now for every row of that user we have the max date for their most recent entry. If you normalize this data model into a second sheet containing JUST the user names you can have a one to many relationship where one row for User searches ALL entries on your grade sheet and returns ONE value for their most recent date. This is a better architectural approach but requires cross references and a centralized maintained user sheet. If you have that great, just swap the references for cross sheet references.

Now that we have the max date, we can get the list of values for the max date for that user. This is simply doing another collect where we want grade back [A] given user [B] and max date (as we defined earlier). You will notice I added a max() around that collection, this is because I am expecting support for multiple entries on the same day so if you scored 5 and 6 on the same day your MAX for that day is 6 NOT 5. Obviously this allows for avg, min, or join if you wanted to see all records on the max date for that user or more complexly or if you wanted to get fancy you can add logic on your collects and aggregates to throw out records based on flags or other data points.

=MAX((COLLECT(A:A, B:B, B@row, C:C, [Max Date]@row)))

Now the final thing to do is to take all our pieces and put them together.

So we copy the formula for Max Date and replace it in the [Max Date]@row area and get

=MAX(COLLECT(A:A, B:B, B@row, C:C, MAX(COLLECT(C:C, B:B, B@row))))

Paul Newcome

Are you able to provide a screenshot for context?

prime_nathaniel

Hello,

I assume this is what you are looking for, made a couple assumptions about your use case but the general approach is easily adaptable. By the looks of it you are probably doing on going certifications or some kind of quality testing.

A = grades as int's, would work as percents or anything sortable
B = palceholder for names [A, B] could be contact card or dropdown doesn't matter so long as its a key
C = date of entry, my version assumes you can have multiple entries on the same day of the same or of different grade values. It you have unique entries per day solution works and this feature would become an safety feature

Max Date:

=MAX(COLLECT(C:C, B:B, B@row))

Value from Max Date for that user:

=MAX((COLLECT(A:A, B:B, B@row, C:C, [Max Date]@row)))

Combined into one formula:

=MAX(COLLECT(A:A, B:B, B@row, C:C, MAX(COLLECT(C:C, B:B, B@row))))

Approach explained:

We want the max date of the list of entries for that user, there could be one entry there could be 1000 entries we only care what the most recent date is as per your requirement "for the highest/most recent date in Column C".
So we use collection of dates, where user = the user of the current row which is MAX(COLLECT(C:C, B:B, B@row)). Now for every row of that user we have the max date for their most recent entry. If you normalize this data model into a second sheet containing JUST the user names you can have a one to many relationship where one row for User searches ALL entries on your grade sheet and returns ONE value for their most recent date. This is a better architectural approach but requires cross references and a centralized maintained user sheet. If you have that great, just swap the references for cross sheet references.

Now that we have the max date, we can get the list of values for the max date for that user. This is simply doing another collect where we want grade back [A] given user [B] and max date (as we defined earlier). You will notice I added a max() around that collection, this is because I am expecting support for multiple entries on the same day so if you scored 5 and 6 on the same day your MAX for that day is 6 NOT 5. Obviously this allows for avg, min, or join if you wanted to see all records on the max date for that user or more complexly or if you wanted to get fancy you can add logic on your collects and aggregates to throw out records based on flags or other data points.

=MAX((COLLECT(A:A, B:B, B@row, C:C, [Max Date]@row)))

Now the final thing to do is to take all our pieces and put them together.

So we copy the formula for Max Date and replace it in the [Max Date]@row area and get

=MAX(COLLECT(A:A, B:B, B@row, C:C, MAX(COLLECT(C:C, B:B, B@row))))

eohlsson@optronicsinc.com

@prime_nathaniel Thanks so much for the quick response! I have tested your proposed solution and it works well, particularly when using a second/roll-up sheet to leverage your proposed "one to many relationship". I already maintain a list of Names in a separate sheet, so it should not involve any additional maintenance. I appreciate your help!

prime_nathaniel

@eohlsson@optronicsinc.com

Anytime, happy it worked out!