Nested COLLECT question
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!
Best Answer
-
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), "")
Answers
-
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), "")
-
Great thank you so much! This worked.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 488 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 153 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!