Return latest value, by date, if criteria match

KK_AT_AT_L

My brain is absolutely melting over this one. I'm not sure if what I'm looking for is too involved, or if I am just truly not understanding how to piece these formulas together (the latter is 100% likely the case).

I'm looking to do the following in a Summary field:

Step 1: Return the latest value from Supplier based on Created

• I've been able to successfully grab this with the following formula:

=INDEX(COLLECT(Supplier:Supplier, Created:Created, MAX(COLLECT(Created:Created, Supplier:Supplier, <>""))), 1)

Step 2: (the real problem) Split into TWO fields, one for Supplier DWG and one for Supplier COR

• I need to distinguish quickly between the two types of sequences and have one Summary field that pulls the latest value that contains COR, and one that pulls the latest value that contains DWG
• Currently we are sorting by Supplier to see the next available number for each Type (and as the Project progresses and the sheet gets beefier, this will get worse)
• This is where I thought maybe also using the existing Type column might help, instead of trying to get it to find just "COR" or "DWG" in the Supplier string [narrator: this did not help]

And this is where I get stuck. Every time I try to add in anything to try and distinguish them so that I can always see what the next number will be for each, it stops working.

I've tried throwing in CONTAINS, MATCH; I've tried Supplier = "COR", etc.; I've tried to get an "IF Supplier = this or that, then return the indexed value", etc.

Have been looking up and reading material throughout the Community (which is how I got that original formula above) for hours and the force is just not with me tonight.

Someone please rescue me from this madness! Thank you 🙂

Nick Korna

Hi @KK_AT_AT_L,

You should be able to do this with something along the lines of:

=INDEX(Supplier:Supplier, MATCH(MAX(COLLECT(Created:Created, Supplier:Supplier, CONTAINS("COR", @cell))), Created:Created, 0))

=INDEX(Supplier:Supplier, MATCH(MAX(COLLECT(Created:Created, Supplier:Supplier, CONTAINS("DWG", @cell))), Created:Created, 0))

Obviously this will rely on the Supplier names not having any fragments of the other Type in (for example, a supplier name "DWG-CORE" would trigger a result from both of the formulas.

Sample data/output (using just date in created in the example):

Hope this helps, but if I've misunderstood anything or you've any problems/questions then let us know!

Nick Korna

Hi @KK_AT_AT_L,

You should be able to do this with something along the lines of:

=INDEX(Supplier:Supplier, MATCH(MAX(COLLECT(Created:Created, Supplier:Supplier, CONTAINS("COR", @cell))), Created:Created, 0))

=INDEX(Supplier:Supplier, MATCH(MAX(COLLECT(Created:Created, Supplier:Supplier, CONTAINS("DWG", @cell))), Created:Created, 0))

Obviously this will rely on the Supplier names not having any fragments of the other Type in (for example, a supplier name "DWG-CORE" would trigger a result from both of the formulas.

Sample data/output (using just date in created in the example):

Hope this helps, but if I've misunderstood anything or you've any problems/questions then let us know!

KK_AT_AT_L

Thank you, @Nick Korna !! This worked flawlessly! You're the best.