I have a sign-out form that I want to find out which ones are out using just formulas.
My thought process is to find the row number of the last instance of each label and if the "out" row number is higher than the "in" row number then the device is currently checked out.
Timestamp Student Name Check out Chromebook Check in Chromebook
2/26/2015 10:33:48 Bjorn Spare1-01
2/26/2015 10:33:59 Fred Spare1-02
2/26/2015 10:34:16 Bjorn Spare1-01
2/26/2015 10:34:39 Conor Spare1-03
2/26/2015 11:57:31 Conor Spare1-01
2/26/2015 11:57:49 Fred Spare1-02
2/26/2015 11:57:59 Bjorn Spare1-02
I am able to find find out all spares that have been checked out using: =sort(UNIQUE(C2:C))
I have been able to find the row number of the last instance of each using: =max(filter(row(C:C),C:C=F2)) (my sort unique function is in column F).
I would like to get my last row formula to work with an arrayformula so that I don't have to rely on manually dragging the formula down.
For example I would like this to work: =arrayformula(max(filter(row(C:C),C:C=F:F))) note the addition of arrayformula and the change from F:2 to F:F.
I am not quite sure why but I get a result of 1000.
Edit: My expected output would be "Last checkout row".
Out Unique Last checkout row
Spare1-01 6
Spare1-02 8
Spare1-03 5
If I can figure out the adding it to the array formula I would also like to find a way to put in the sort (unique function so that it becomes a single formula which I enter in the column number and it spits out the last row numbers of each unique term).
Here is a link to a document that I am playing with: https://docs.google.com/spreadsheets/d/1jC0RPxUZSt7BCHRQI5vBXvoANbQlu9CjDNr2VV-MOOI/edit?usp=sharing
Edit (final formula used) (I can't thank @JVP enough for the help with this):
=ArrayFormula( if(vlookup(unique(filter(C2:C, len(C2:C))), sort({C2:C, A2:B, row(A2:A)},4,0), 4, 0) > iferror(vlookup(unique(filter(C2:C, len(C2:C))), sort({D2:D, A2:B, row(A2:A)},4,0),4,0),0), vlookup(unique(filter(C2:C, len(C2:C))), sort({C2:C, A2:C, row(A2:A)},5,0), {2,4,3}, 0), ))
For your desired output (including the names), try:
=ArrayFormula({unique(filter(C2:C, len(C2:C))), vlookup(unique(filter(C2:C, len(C2:C))), sort({C2:C, A2:B, row(A2:A)},4,0), 4, 0)})
Following your thought process (ultimately you want to see if a device is currently checked in or out, right ?), try:
=ArrayFormula({unique(filter(C2:C, len(C2:C))), if(vlookup(unique(filter(C2:C, len(C2:C))), sort({C2:C, A2:B, row(A2:A)},4,0), 4, 0) > iferror(vlookup(unique(filter(C2:C, len(C2:C))), sort({D2:D, A2:B, row(A2:A)},4,0),4,0),0), "checked out", "checked in")})