How to pick the latest date from Table 1 and enter it to the cell of Table 2?

Aliaksandr Ilyuk

Table 1 is a list of products that contains among others columns Trade Name, Country, Crop and Last Modified (Auto). Within the rows, one trade name can refer to different countries or crops.

In Table 2 there are only Trade Name and Date columns.

I need to choose the latest date from the rows of Table 1 referred to the specified trade name and place it in one cell of the date column of Table 2.

Thank you in advance

Alex

Paul Newcome

Here's a breakdown of the COLLECT. Basically the way it works is you are collecting a set of data based on specific criteria that you set. So the first range is the range of data you want to collect. Then you would use a series of range/criteria to determine what cells from the first range you want.

So using your above as an example, we want to collect data from the Last Modified column of Table 1

=COLLECT({Protocol approval Last Modified Column},

Then we want to collect those cells based on the range of the Trade Name column of Table 1

=COLLECT({Protocol approval Last Modified Column}, {Protocol approval Trade Name Column},

where the criteria of it matching the data in the parent row of the Task column

=COLLECT({Protocol approval Last Modified Column}, {Protocol approval Trade Name Column}, @cell = PARENT(Task@row))

Now we want the MAX of the data we pulled using our COLLECT, so we wrap the COLLECT in the MAX and see how it works.

=MAX(COLLECT({Protocol approval Last Modified Column}, {Protocol approval Trade Name Column}, @cell = PARENT(Task@row)))

Paul Newcome

You would follow the pattern

=MAX(COLLECT(range to collect, criteria range 1, criteria 1, criteria range 2, criteria 2, ..................................)

=MAX(COLLECT({Protocol approval Last Modified Column}, {Protocol approval Trade Name Column}, @cell = PARENT(Task@row), {Protocol approval Country of Registration Column}, @cell = "Ukraine"))

Paul Newcome

Are you able to provide some screenshots with sensitive/confidential data removed, blocked, or replaced with "dummy data" as needed?

Based on your question, I believe there may be a solution using a MAX/COLLECT formula, but I want to be sure I am understanding you correctly.

Aliaksandr Ilyuk

Hello Paul.

This is Table 1:

This is Table 2:

I need the latest dates from Table 1 to be placed in the corresponded cells of Table 2.

I have thought about MAX & COLLECT formulas but I hardly understand the logic and the syntax of the last one. Could you help with this?

Thank you.

Paul Newcome

Here's a breakdown of the COLLECT. Basically the way it works is you are collecting a set of data based on specific criteria that you set. So the first range is the range of data you want to collect. Then you would use a series of range/criteria to determine what cells from the first range you want.

So using your above as an example, we want to collect data from the Last Modified column of Table 1

=COLLECT({Protocol approval Last Modified Column},

Then we want to collect those cells based on the range of the Trade Name column of Table 1

=COLLECT({Protocol approval Last Modified Column}, {Protocol approval Trade Name Column},

where the criteria of it matching the data in the parent row of the Task column

=COLLECT({Protocol approval Last Modified Column}, {Protocol approval Trade Name Column}, @cell = PARENT(Task@row))

Now we want the MAX of the data we pulled using our COLLECT, so we wrap the COLLECT in the MAX and see how it works.

=MAX(COLLECT({Protocol approval Last Modified Column}, {Protocol approval Trade Name Column}, @cell = PARENT(Task@row)))

Aliaksandr Ilyuk

Great, it works. Thanks a lot!

Paul Newcome

Happy to help! 👍️

Please don't forget to mark the most appropriate response(s) as "helpful" so that others looking for a similar solution can know that one may be found here.

Aliaksandr Ilyuk

If I want to add one more criteria (max. date should refer to Ukraine only), I need to collect data from {Protocol approval Country of Registration Column}. What should I add to the formula?

=MAX(COLLECT({Protocol approval Last Modified Column}, {Protocol approval Trade Name Column}, @cell = PARENT(Task@row); [???]))

Paul Newcome

You would follow the pattern

=MAX(COLLECT(range to collect, criteria range 1, criteria 1, criteria range 2, criteria 2, ..................................)

=MAX(COLLECT({Protocol approval Last Modified Column}, {Protocol approval Trade Name Column}, @cell = PARENT(Task@row), {Protocol approval Country of Registration Column}, @cell = "Ukraine"))

Aliaksandr Ilyuk

Currently the formula is the following:

=MAX(COLLECT({Protocol approval Last Modified Column}, {Protocol approval Trade Name Column}, @cell = PARENT(Task@row), {Protocol approval Country of Registration Column}, @cell = "Ukraine"))

and it perfectly works in green cell.

In case of a bit more complicated hierarchy of tasks, how to set up the formula (for the yellow cell) to capture the matching criteria from the highest level?

Paul Newcome

You are going to want to add in 2 columns. Hierarchy and [Task Helper]. Both will be text/number type.

In Hierarchy:

=COUNT(ANCESTORS(Task@row))

In [Task Helper]:

=IFERROR(INDEX(ANCESTORS([Task Helper]@row), MATCH(0, ANCESTORS(Hierarchy@row), 0)), [Task Helper]@row)

Then adjust your formula to look more like this...

=MAX(COLLECT({Protocol approval Last Modified Column}, {Protocol approval Trade Name Column}, @cell = [Task Helper]@row, {Protocol approval Country of Registration Column}, @cell = "Ukraine"))