Get the latest value of multiple matching entries.

Speigel

Looking for the right formula to look at the list of entries and IF the Contract Number AND Invoice Line Item Number match. The example screenshot shows 4 lines. They're all the same contract, but the 4th line item is a duplicate entry of "A". SO I want the "Latest Contract Amount" column to keep the latest value entered for those matching rows. In this case Rows 1 and 4 would show the amount entered last on the 19th, of $5000.00.

James Keuning

I can't edit my comment, so adding this one:

Create a column called Helper, and use this formula: =[Contract Number]@row + [Invoice Line Number]@row

This formula combines your Contract Number and your Invoice Line Number. Once you create this, you can hide it.

Create a column called Max (or whatever) and use this formula: =MAX(COLLECT([Contract Amount]:[Contract Amount], Helper:Helper, Helper@row))

What this formula does is Collect the Contract Amounts from the Contract Amount column where the record's value is the same as the value in the Helper column. Imagine Smartsheet stores this in its memory, and then the Max asks Smartsheet to tell you which one is biggest.

James Keuning

Answering quick because I need to run, but have you explored using Collect and Max?

https://help.smartsheet.com/function/max

Create a helper column to concatenate Contract Number AND Invoice Line Item Number, Collect those values, and use Max.

James Keuning

I can't edit my comment, so adding this one:

Create a column called Helper, and use this formula: =[Contract Number]@row + [Invoice Line Number]@row

This formula combines your Contract Number and your Invoice Line Number. Once you create this, you can hide it.

Create a column called Max (or whatever) and use this formula: =MAX(COLLECT([Contract Amount]:[Contract Amount], Helper:Helper, Helper@row))

What this formula does is Collect the Contract Amounts from the Contract Amount column where the record's value is the same as the value in the Helper column. Imagine Smartsheet stores this in its memory, and then the Max asks Smartsheet to tell you which one is biggest.