Display most recent date in a row based on criteria in a column?

[Jake_Stromberg]

Hi there,

I’m new to Coda and trying to figure out if I can have a column that gives me the Max value from a list, based on the values in another column. In Excel parlance, a MAXIF formula.

Here’s a screenshot:

I’d like to get the Latest ETA per Order from the ETA column based on the Order #.

For instance, the top two rows have the same Order #. The latest date from the two rows is 10/27/2022. I would like that date to display for all rows containing the same Order #.

Thanks for your help!

[Piet_Strydom]

HI Jake,

Welcome to Coda! Yes, it is slightly different to Excel, but once you get the hang of it, you will never look back.

The formula in Coda is Max().
In the example below, I give formulas both for the Max for the whole column, and how to get the max for a group of entries in a column.

The screenshot is from my Formula Reference Manual below. (Still a work in progress… but cover about 40 formulas so far.)

Extended Formula Helplist · Rambling Pete's Formula Reference Manual

Regards
Rambling Pete

[Jake_Stromberg]

Thanks for this! Looks like a great resource. The only thing I’m stuck on with the solution is what [tabAverage Formula] is referring to.

[Jake_Nguyen]

I love the Coda formula it’s just like Python, so English-esque

Programatically this would be something like

Open Orders.Filter(Order# = thisrow.Order#).max(ETA)

In English, this is something like: “For each row in the Open Orders table, look at all the rows where the order number equals to the row’s order number. And then find the max ETA out of all of those rows.”

[Piet_Strydom]

Hi Jake,

The name of the table is tabAverage Formula.

So what the column formula says in English is: Go to table tabAverageFormula, then filter on the column colTopic. And as Jake describes well “look at all the rows where the order number equals to the row’s order number. And then find the max of those rows” (It is not easy to describe eloquently in natural language!)

Regards
Piet

[Jake_Stromberg]

Got it. Thanks! This works perfectly. Actually, this example really helped me understand Coda’s formula structure generally. Appreciate it!