Collect Latest Score given date, name & score data

Mako_NZ

Hi I'm trying to collect the latest score for a given customer into my metrics sheet for my dashboard.

The Metrics sheet looks like this:

The source collection sheet (fed by a form) looks like this:

There are hundreds of customers, and all scores are in a single column, help would be great - thanks in advance, M

Kelly Moore

Hey @Mako_NZ

Based on the screenshot I made an assumption that the most recent entry for a customer will be the last entry of that customer. In other words, your sheet is always in chronological order marching down the sheet. If my assumption is incorrect, a different approach will be required. If I am correct, try the formula below.

Note I renamed the references to make it clear which columns I was using. You will need to make sure you're using your actual cross references.

=INDEX(COLLECT({Customer Meeting Rating Repository - FY25 Customer Satisfaction Rating}, {Customer Meeting Rating Repository - FY25 Customer Satisfaction Rating}, @cell > 0, {Customer Meeting Rating Repository - FY25 Customer Organization}, "RedShield"), COUNTIFS({Customer Meeting Rating Repository - FY25 Customer Satisfaction Rating}, @cell > 0, {Customer Meeting Rating Repository - FY25 Customer Organization}, "RedShield"))

This formula works by using the COUNTIFS formula to leverage the row_index portion of the INDEX function. The row_index selects the row of the 'list' that is generated. The COUNTIFS counts the total number of items in that list, and that number is the last instance of the data.

Does this work for you?
Kelly

Mako_NZ

The formula I tried to work with:
=MIN(COLLECT({Customer Meeting Rating Repository - FY25 Range 2}, {Customer Meeting Rating Repository - FY25 Range 1}, >0, {Customer Meeting Rating Repository - FY25 Range 4}, "RedShield"))

Response: '0', should have been '5'

Kelly Moore

Hey @Mako_NZ

Based on the screenshot I made an assumption that the most recent entry for a customer will be the last entry of that customer. In other words, your sheet is always in chronological order marching down the sheet. If my assumption is incorrect, a different approach will be required. If I am correct, try the formula below.

Note I renamed the references to make it clear which columns I was using. You will need to make sure you're using your actual cross references.

=INDEX(COLLECT({Customer Meeting Rating Repository - FY25 Customer Satisfaction Rating}, {Customer Meeting Rating Repository - FY25 Customer Satisfaction Rating}, @cell > 0, {Customer Meeting Rating Repository - FY25 Customer Organization}, "RedShield"), COUNTIFS({Customer Meeting Rating Repository - FY25 Customer Satisfaction Rating}, @cell > 0, {Customer Meeting Rating Repository - FY25 Customer Organization}, "RedShield"))

This formula works by using the COUNTIFS formula to leverage the row_index portion of the INDEX function. The row_index selects the row of the 'list' that is generated. The COUNTIFS counts the total number of items in that list, and that number is the last instance of the data.

Does this work for you?
Kelly

Mako_NZ

Thankyou for your response Kelly - that worked. Your assumption is correct, just re-referenced them and correct result. Only thing is - I have a calc I don't understand lol - but all good.

Mako_NZ

Sorry, just to make it more complex, I am trying to remove an 'INVALID' response when theres no source data. I added an IFERROR:

=IFERROR(INDEX(COLLECT({Customer Meeting Rating Repository - FY25 Range 2}, {Customer Meeting Rating Repository - FY25 Range 2}, @cell > 0, {Customer Meeting Rating Repository - FY25 Range 4}, "RedShield"), COUNTIFS({Customer Meeting Rating Repository - FY25 Range 2}, @cell > 0, {Customer Meeting Rating Repository - FY25 Range 4}, "RedShield")), "")

But it still shows as INVALID? its odd because it works on other formulas?

Kelly Moore

Hey @Mako_NZ

I was trying to make it so you didn't have to add the helper column but a helper column will give you a more reliable formula. You will also need either the Created by column or the autonumber [Row ID] column. In my formula below I used the [Row ID] column. If you already have either of these in the Source sheet, use whichever one you have. If you don't have either, you'll need to add one- in addition to the helper column below.

Add a new helper checkbox column to your SOURCE sheet - you can hide the column and/or sling it to the far right out of the way. This column will flag the most recent row per customer. I called my helper column MaxRow. You can call it what you like

MaxRow

=IF([Row ID]@row = MAX(COLLECT([Row ID]:[Row ID], [Customer Organization]:[Customer Organization], [Customer Organization]@row)), 1)

Use this MaxRow in your INDEX(COLLECT) that you need in your Target sheet. Because the max will be flagged, you will not need the countifs.

=INDEX(COLLECT({Customer Meeting Rating Repository - FY25 Customer Satisfaction Rating}, {Customer Meeting Rating Repository - FY25 Customer Satisfaction Rating}, @cell > 0, {Customer Meeting Rating Repository - FY25 Customer Organization}, "RedShield", {Customer Meeting Rating Repository - FY25 MaxRow}, @cell=1), 1)

Once you make sure this formula works for you then you can wrap it in an IFERROR, if desired.

Will this work for you?

Kelly

Mako_NZ

Many thanks Kelly, your first response solved the key problem so all good :) (marked as answered :) Cheers!