Nested COLLECT question

programd2manage

I need help with creating the formula for the [PrevHours] column (below).

The expected logic for this formula would be: for a given row's ColValue, find the max date from all instances of ColValue, then find that row's Col2Value.

For example:

Project | Date | Hours | PrevHours

XYZ | 5/1/23 | 10 | ""

XYZ | 5/2/23 | 15 | 10

ABC | 5/1 | 100 | ""

XYZ | 6/1/23| 100 | 15

I want [PrevHours] to refer the previous latest date's entry for hours

Thank you!

Leibel S

@programd2manage

Add a date type column 'Previous Date' with the below column formula

=MAX(COLLECT(Date:Date, Date:Date, @cell < Date@row, Project:Project, @cell = Project@row))

The formula for previous hours is:

=IF([Previous Date]@row <> "", INDEX(COLLECT(Hours:Hours, Project:Project, @cell = Project@row, Date:Date, @cell = [Previous Date]@row), 1), "")

Leibel S

@programd2manage

Add a date type column 'Previous Date' with the below column formula

=MAX(COLLECT(Date:Date, Date:Date, @cell < Date@row, Project:Project, @cell = Project@row))

The formula for previous hours is:

=IF([Previous Date]@row <> "", INDEX(COLLECT(Hours:Hours, Project:Project, @cell = Project@row, Date:Date, @cell = [Previous Date]@row), 1), "")

programd2manage

Great thank you so much! This worked.