What is the best way to formulate a check in/check out status sheet similar to a library database?

Question

I have been trying to build a checkout system for devices at work using spreadsheets and google forms. The google forms work wonderfully, and I'm able to get all the data I need in a spreadsheet that I can look through manually if a device turns up missing. I was hoping the process of taking this data and creating a sheet that would give the status of device as "IN" or "OUT" would be simple, but I can't seem to get the results I'm looking for.

From Google forms it goes into a sheet with timestamp, device, and who checked it out. There is a separate form and sheet when checking the device back in.

From there I created a new "STATUS" sheet that I referenced the other two to get the most recent time stamp, which device, and who checked it out.

I'm trying to reference the cells with those changing values to trigger the cells next to each device as "IN" or "OUT" but I can only have one IN and one OUT at a time.

In this sheet I am using VLOOKUP to find the latest device checked out using =VLOOKUP(MAX(TIMEOUT),CHECKOUT!A2:B,2,FALSE)

To change the values that are currently FALSE I am referencing the cell with the returned value of the VLOOKUP using =IF(E4=C24,"OUT",IF(E11=C24,"IN"))

However, whenever I check out another device it changes the next device OUT, but returns the previous checkout to FALSE.

I know nesting functions can screw sheets up when used incorrectly, so I was hoping for some guidance because I'm not too good at this apparently. Any help is appreciated!

Answer 1

Updated Solution

Uses the sheets named blind_STATUS , blind_IN ,and blind_OUT

blind_STATUS sheet has:

• A single entry for each unique Device,
• the device's current Status,
• the Timestamp associated with the status,
• the duration a device has been Out (hrs),
• the ability to do some primary and secondary sorting via drop-menus,
• the current sort is rendered at top of the page to avoid confusion with defaults, redundant and/or contradictory options, etc.

Sample Status Page ^{[ Link ]}

Sorting by "STATUS ▼"	then by	"OUT (HRS)▲"				Sort [ default ▲ /▼ ]	By	Order
						Primary [ Timestamp ▼ ]	Status	Descending
Timestamp	Device	Employee	Status	Out (hrs)		Optional [                 ▲ ]	Out (hrs)	Ascending
Jan-10-2023 6:07 AM	TC26-05	Wheeler, Lena	OUT	66.09
Jan-10-2023 5:07 AM	TC26-21	Maldonado, Annabelle	OUT	67.09
Jan-09-2023 6:10 PM	TC26-10	Morgan, Daphne	OUT	78.04
Jan-08-2023 6:05 PM	TC26-23	Mathews, Lamont	OUT	102.13
Jan-10-2023 5:08 AM	TC26-20	Villanueva, Barbara	IN
Jan-08-2023 6:20 PM	TC26-22	Bishop, Saniyah	IN

Sample Status Page

Formula

=SORT(
   BYROW(_MYSORT2({_MYSORT2(_out, "OUT");_MYSORT2(_in, "IN")},), 
         LAMBDA(_r, IF(INDEX(_r,,4)="OUT",
               {_r,(NOW()-INDEX(_r,,1))*24}, _r))),
   IFERROR(MATCH(_byPri,_cols,0),1),
   IF(_ordPri="Ascending",1,0),
   IFERROR(MATCH(_bySec,_cols,0),IFERROR(MATCH(_ordSec,_cols,0),1)),
   IF(_ordSec="Descending",0,1)
   )

Formula

Named Ranges

Name	Range	Notes
_cols	blind_STATUS!A3:E3	Column Headings
_in	blind_IN!A2:C1000	Checkin Data Columns
_out	blind_OUT!A2:C1000	Checkout Data Columns
_byPri	blind_STATUS!H2	Primary Sort Column
_bySec	blind_STATUS!H3	Secondary Sort Column
_ordPri	blind_STATUS!I2	Primary Sort Order
_ordSec	blind_STATUS!I3	Secondary Sort Order

Named Function

Solution uses a Named Function for the custom SORTN to reduce the length and complexity of the parent formula.

There are two options below based on the possible source data.

_MYSORT   (v1)
Simpler and requires the source data include a field identifying if coming from check-in or check-out.

Timestamp	Device	Employee	Status
1/8/2023 18:00:00	TC26-23	Melton, Malia	"IN"

_MYSORT =Lambda(x, 
         SORTN(SORT(x,1,0),COUNTA(INDEX(x,,1)),2,2,1))
         (FILTER(_in, INDEX(_in,,1)<>""))

_MYSORT2   (v2)
Longer but can be used regardless if the source data includes a field identifying if coming from check-in or check-out, or if it does not.

Timestamp	Device	Employee
1/8/2023 18:00:00	TC26-23	Melton, Malia

_MYSORT2 =BYROW(LAMBDA(x, SORTN(SORT(x,1,0),
                COUNTA(INDEX(x,,1)),2,2,1))
                (FILTER(_data, INDEX(_data,,1)<>"")), 
          LAMBDA(y, IF(_label<>"",{y, _label},y)))

Benefit in Practice

     you can use this formula

=_MYSORT2({ _MYSORT2(_out, "OUT");
            _MYSORT2(_in, "IN")
           },)

    as a replacement for this formula

=LAMBDA(x, SORTN(SORT(x,1,0),COUNTA(INDEX(x,,1)),2,2,1))
 ( BYROW(
     {
       BYROW(
         LAMBDA(x, SORTN(SORT(x,1,0),COUNTA(INDEX(x,,1)),2,2,1))
         (FILTER(_out, INDEX(_out,,1)<>"")),
         LAMBDA(y, {y, "OUT"})
       );

       BYROW(
         LAMBDA(x, SORTN(SORT(x,1,0),COUNTA(INDEX(x,,1)),2,2,1))
         (FILTER(_in, INDEX(_in,,1)<>"")),
         LAMBDA(y, {y, "IN"}))
      },

     Lambda(x, SORTN(SORT(x,1,0),COUNTA(INDEX(x,,1)),2,2,1)))
  )

  

---

NOTE

My first approach did not use SORTN, but rather MAXIFS. In trying to further simplify the formula and add secondary dynamic Sort By options, I switched from MAXIFS to SORTN inspired by doubleunary's approach, and he should be given credit for that answer.

My older formula that I updated:

=SORT(
   BYROW(UNIQUE(FILTER(_oDev,ROW(_oDev)>1)), 
      LAMBDA (r, IF(r<>"", 
         IF(MAXIFS(_oTime, _oDev, r)>MAXIFS(_iTime, _iDev, r),
            { r, "OUT", MAXIFS(_oTime, _oDev, r), 
              INDEX(_oEmp, MATCH(MAXIFS(_oTime, _oDev, r),_oTime)),
              (NOW()-MAXIFS(_oTime, _oDev, r))*24 },
            { r, "IN",MAXIFS(_iTime, _iDev, r), 
              INDEX(_iEmp, MATCH(MAXIFS(_iTime, _iDev, r),iTime)) }),))),
   IFERROR(MATCH(_sortBy,_headings,0),1),
   IF(_sortOrd="Descending",0,1))

Answer 2

To get a list of all devices that appear in CHECKOUT and CHECKIN, so that only the most recent status of each device is shown, use sortn(), like this:

=sortn( 
  sort( 
    { 
      CHECKOUT!A2:C, left("out", 9 * len(CHECKOUT!A2:A)); 
      CHECKIN!A2:C, left("in", 9 * len(CHECKIN!A2:A)) 
    }, 
    1, false  
  ), 
  9^9, 2, 
  2, true 
)

See your sample spreadsheet.

If you want to see these results in a table like the one in the STATUS tab, use the vlookup() function, like this:

=arrayformula( 
  iferror( 
    vlookup(A2:A24, Solution!B2:D, columns(Solution!B2:D), false) 
  ) 
)

See the STATUS2 tab.