0

I am building a member database for the non-profit youth center I volunteer with. I did something similar using Google sheets and Forms for a former company in managing their inventory across multiple locations.
The tab that shows a date/time stamp in Col B populates from a scan of the members ID card which is the number in Col A. I want to link the scan of the member ID to the In/Out column (Col D) of the member list. The tricky part that I can't figure out is how to have it recognize the most recent scan using that ID # and how to differentiate between an In and Out scan........any ideas or feedback is greatly appreciated.
I'm somewhat familiar with formula structure but know very little about script. Totally willing to learn though. The Date/Time stamp is populate through a script I found in an online tutorial.

https://docs.google.com/spreadsheets/d/1aqKuXSKo_gxS-UwUCcyYjyMFExdlti1jdHzNloAbF5U/edit#gid=2028624197

I tried using vlookup but don't know how to set the search criteria properly to return the In/Out value I'm looking for.

rene
  • 41,474
  • 78
  • 114
  • 152
Bobby Gumpright
  • 1
  • 1
  • https://docs.google.com/spreadsheets/d/1aqKuXSKo_gxS-UwUCcyYjyMFExdlti1jdHzNloAbF5U/edit?usp=sharing – Bobby Gumpright Apr 07 '23 at 02:49
  • Dealt with such a scan-log system directly and here's why logging them as in/out based on alternate log values didnt work out for us. there were scenarios where ppl scanned it twice for a single instance of in/out bcoz they probably think the first one didnt go through or whatever. Now you have duplicates which messes up the alternate in/out formulas. Then there's the scenario of ppl skipping to scan for an instance bcoz there's some issue with scan system/ID OR most likely they are scan-tailgating someone with valid ID which again makes the formulas go haywire! – rockinfreakshow Apr 09 '23 at 17:35

1 Answers1

0

You can try with XLOOKUP looking from bottom to find the last value; and if the amount of appearances is ODD it would be IN or if not it would be OUT (right?)

    =BYROW(TOCOL(A2:A),LAMBDA(id,IF(ISNA(MATCH(id,'ID Scan'!A:A,0)),"",
IF(ISODD(COUNTIF('ID Scan'!A:A,id)),"IN ","OUT ") & TEXT(XLOOKUP(id,'ID Scan'!A:A,'ID Scan'!B:B,,0,-1),"DD/MM/YYYY hh:mm:ss"))))

enter image description here

Martín
  • 7,849
  • 2
  • 3
  • 13