0

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.

enter image description here

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.

=if(countifs(D2:D9,D2,C2:C9,"")>0, true)

Then I made an array in column F showing only rows with false values in E

=filter(A2:D9,not(E2:E9))

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.

To be clear, I want the filtered results to show only one row per item which is currently in stock, like this: enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.