Create a seat assigner with Excel and RFID

Anonymous
2020-04-16T23:26:27+00:00

Hi, 

Due to Covid19, we are tracking who sits with who in the cafeteria.

We have a simple Excel file that assigns a seat in the cafeteria to the employees based on the RFID. It is very simple, We have a Seat column with seats from 1-135, they scan their badge, the ID number appears on the ID column, then there is a name column with a Vlookup based on the ID that enters the name of the employee and that is it. 

The issue we have is that, sometimes it assigns a seat that is still taken. I cannot think of a way in which the employee scans their badge again and the record is deleted from the seat number so that it is now free for someone else :S and doing that without losing the original records of where the person sat. 

Any ideas would be highly appreciatted

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2020-04-23T05:33:48+00:00

@ Debbie.

I have no scanners here, so I typed in the ID numbers

1- Before the start

The SCAN sheet will look like this, The scanner will be focused on cell C3 Named range ScanID 

2- After the first 10 scan (employees)

After the first 10 scan (employees) on each sheet 

RECORD/TRACKING before any check-out.

3- After some of the staff check out

Conditional Formatting is applied to cells 

the ones in Yellow shows those who are taking more than 30 min in their lunchtime and obviously haven't been checked out.

Hope this helps

Regards

Jeovany

Was this answer helpful?

4 people found this answer helpful.
0 comments

50 additional answers

Sort by: Newest
Most helpful Newest Oldest
  1. Anonymous
    2020-04-18T08:36:06+00:00

    BTW

    You could also explore the possibility to create 6 groups with approximately 134 employees each one

    and give to those groups their times and seats for the break.

    Do let me know in order to move further with your query

    Was this answer helpful?

    0 comments
  2. Anonymous
    2020-04-18T00:58:19+00:00

    Dear Debbie

    Regarding: "Is it a good idea to have two workbooks? ..."

    IMHO: **I think,**Instead of one problem, you might end up with two.

    Maybe, 1 computer and 2 scanners, the IT team could set up the two scanners for one PC.

    Regarding: The code and your requirements for the workbook

    I'll do my best to help you, No problem.

    Now let's put your requirements and goals aside

    Regarding: COVID restrictions and the whole idea behind the control, tracking**and keep a record of who sat next to whom.

    IMHO

    My suggestion:

    To Assing the same seat to the same person all the time.

    According to your total employees and seats, you could assign each seat to the same 5 to 6 employees numbers.

    So,

    Seat#1 could be for Employees ID-01,02,03,04,and 05

    Seat#2 could be for Employees ID-06,07,08,09, and10

    and so on.

    It would allow you to control the time and let them know when their assigned seat is available.

    Also, I think they might feel more confident if they sat on the same seat knowing that the previous one will be the same person all the time and not a random previous user.

    You could assign also a time for the lunch break for these 6 employees in advance. And let them know. 

    That might avoid long queuing for lunch

    It could help to reduce further the risk of spreading the virus

    Once again is my modest suggestion.

    It might don't work in your place and scenario, 

    It is up to you guys.

    Anyway

    I'll work on the solution to your problem.

    Regards

    Stay Healthy

    Was this answer helpful?

    0 comments
  3. Anonymous
    2020-04-17T19:49:24+00:00

    The RFID only asks you to place the cursor in an empty cell and it will start entering the data there. 

    The macro all it does is to enter the time and date: 

    If Not Application.Intersect(Target, Range("B:B")) Is Nothing Then

    Range("f" & Target.Row) = Date

    Range("e" & Target.Row) = Format(Now, "hh:mm")

    Sheet4.Protect Password:="wet"

    Is it a good idea to have two workbooks? One for entering and one for exiting? Two computers, one in the entrance and one in the exit. 

    What they need it: 

    1. To keep record of who sat next to whom, forever.
    2. To have another sheet that tells them where to seat and that assigns empty seats only. So, it needs to know that the person who was sitting in 3 left and now that can be assigned to someone else.

    Is there any software that I can use to achieve this? In case Excel cannot do it

    Was this answer helpful?

    0 comments
  4. Anonymous
    2020-04-17T16:04:41+00:00

    Hi Debbie

    If you could copy and paste here in a new post the code (macro) of the RFID reader

    we could do something in this regard.

    We could create a macro that will count the number of times the same badge passes the scanner,

    insert the time out on the new column and make the seat available once again.

    Regards

    Was this answer helpful?

    0 comments