Help with match to second value as sign on time

[Perry.350]

Hi All,

Firslty, I'm a basic user of Excel and I need help with a formula, Please

We are using a barcode scanner to sign in employees, this part we have be able to do, however, we have "Split Shifts", so we need to be able to cancel out the first sign on and search for the second sign on later i the day,

All barcodes are being signed in to 1 column, so, I'm guessing the formula will need to look at previously scanned codes to be able to look for the second code further down the already signed column.

Hope this makes sence.

I was thinking of using a sign in and sign off process to capture both as a sign in, but not sure if you can do this in the same barcode input column as all info points to a 2 column setup, 1 for sign in and 1 for sign off.

any help will be greatly appreciated.

[FDibbins]

Welcome to the forum

1. Please read the yellow banner above and upload a sample WB.

2. You may end up having to use a helper column where you count the entries for a specific individual, then base your search on that.

[Perry.350]

Thank you, I have uploaded a sample.

[TMS]

Try:

=LET(u,UNIQUE(H2:H12),m,MAP(u,LAMBDA(r,TAKE(FILTER($E$2:$E$12,$H$2:$H$12=r),-1))),HSTACK(u,m))

[HansDouwe]

Here is another solution:

Please try

=LET(b,H2:H12,t,E2:E12,u,UNIQUE(b),HSTACK(u,MAXIFS(t,b,u)))

[TMS]

@Hans: Nice. I hate it when you do that