Retrieve previous entry on a cell where column meets a criteria

Enrique Rodriguez

I'm trying to retrieve a value from a cell where a different cell meets a criteria. Entries will be done for each week for several locations. After a series of entries a Gap% will be calculated for that row. When entering another record for the same location for the following week, I need a cell in that same row to retriever the last calculated Gap% and populate the "Compare to Last" cell to be able to analyze both gaps side to side. Entries will not necessarily be capture for every single week but they might. Locations might not have several entries, but they might. I just need to retrieve the value of the last entry for that same location "IF" there is one. Please help. Below what the results should be:

prime_nathaniel

Hello @Enrique Rodriguez

You can accomplish this use case using the collect function to get a subset of your data to be used in formulas.

You can see in my example below I have

location A and B,
Date 4/1 to 4/4
Gaps % reports
Last Value which is

=IF(MAX(COLLECT(Date:Date, Location:Location, Location@row, Date:Date, <Date@row)) = "", "", MAX(COLLECT(Date:Date, Location:Location, Location@row, Date:Date, <Date@row)))

No Previous which is just for error handling and you could bake it into your last value

=[Last Value]@row = ""

And then compare to last

=IF([No Previous]@row, "", MAX(COLLECT([Gap %]:[Gap %], Date:Date, [Last Value]@row)))

I also chose to add error handling on all columns with the IF then formula to report blank "" string, this is optional and doesn't really change how the solution would be done.

Explanation of Solution:
You need to first get a list of all dates for a location AND do not include the current row report with that which is

COLLECT(Date:Date, Location:Location, Location@row, Date:Date, <Date@row))

If you get multiple reports on the same day you would need to add ⇐ to Date@row and use another criteria to like time of day to get the actual final report (approach doesnt change, just add more criteria)

Once you have the collect list of dates you can then get the MAX date of that list which is the most recent date OTHER than the date of your current row.

MAX(COLLECT(Date:Date, Location:Location, Location@row, Date:Date, <Date@row))

Now that I have the last value date for everything I can get the data for that last date

COLLECT([Gap %]:[Gap %], Date:Date, [Last Value]@row)

Should there be more than one entry per date, I add a max() to it so I only get the highest value

MAX(COLLECT([Gap %]:[Gap %], Date:Date, [Last Value]@row))

Then because I want error handling for the FIRST date where no previous entry exists. I can report back "" rather than 0% for no previous.

=IF([No Previous]@row, "", MAX(COLLECT([Gap %]:[Gap %], Date:Date, [Last Value]@row)))

Enrique Rodriguez

Thanks for your help. I decided to take a different approach. I created a unique ID for each row based on the week# and Location Name, then on the previous gap% I created a formula that searches for the previous entered Gap% and brings it back to that row.

=IFERROR((INDEX([Gap %]:[Gap %], MATCH((([Calendar Week Auto]@row - 1) + Location@row), [Row ID]:[Row ID], 0))), "")

Thanks again.