I'm making a Google Sheet to track rental inventory. Each time a rental comes back in, I will fill in the
Date Returned. When that same item goes out again, I will copy and paste it to a new row, change the
Date Out and leave the
Date Returned blank. I am trying to create a filter view to show items currently in stock. This means for each unique
Item Name it needs to test if the highest
Returned Date is not empty.
The filter to show active rentals is simple: I just test whether
Returned Date is empty. But the in-stock filter is more complicated because I need to ignore all the old rows when there is an active one. Does this make sense? I think I'm almost there. I made a helper column E which shows true if the item is currently out.
Then I made an array in column F showing only rows with false values in E
But I can't figure out how to limit this to show only unique values. Then I have to figure out how to put all these formulas into a filter view.
I shared a simplified version of the sheet at this link. Any advice would be appreciated.