Get the latest value, based on a date, from a specific value.

Msevela

I know that i will probably have to use Index, Max and Collect. I Just don't know how.

This is the "Registry" table which is going to display the last value, based on the last date, based on the product SKU. Only with "In"s.

And This is the "Movements table" which logs all the data.

(This structure has the objective to record the last price wich the material was purchased.)

As i've said, i tried using Max Collect Index, but with no success.

Mike TV

@Msevela

Ok. Swear I've got it this time.

First you need to create a helper column on the target sheet to give you the max date of an "In" on a SKU. Example:

Max Date formula (format column as a date column):

=MAX(COLLECT({Created}, {In/Out}, ="In", {SKU}, =SKU@row))

Price formula:

=MAX(COLLECT({Price}, {SKU}, =SKU@row, {In/Out}, ="In", {Created}, =[Max Date]@row))

That should do it.

Mike TV

@Msevela

I think this gets us there:

=MAX(COLLECT({Price}, {SKU}, =SKU@row))

Mike TV

@Msevela

My results came out like this:

Mike TV

@Msevela

Oops. I forgot your "In" condition that was needed. Formula update:

=MAX(COLLECT({Price}, {SKU}, =SKU@row, {In/Out}, ="In"))

Results:

Mike TV

@Msevela

Oops again. That's not working. Back to the drawing board.

Mike TV

@Msevela

Ok. Swear I've got it this time.

First you need to create a helper column on the target sheet to give you the max date of an "In" on a SKU. Example:

Max Date formula (format column as a date column):

=MAX(COLLECT({Created}, {In/Out}, ="In", {SKU}, =SKU@row))

Price formula:

=MAX(COLLECT({Price}, {SKU}, =SKU@row, {In/Out}, ="In", {Created}, =[Max Date]@row))

That should do it.

Msevela

@Mike TV

Thank you so much, it worked like a charm.