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: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2020-04-17T00:53:05+00:00

    Hi Debbie

    My suggestion so far

    1- Create a Time Out (Lunch Time End) column that will be filled once the employee scans his badge again

    2-Once it happens, then to use IF conditional functions with COUNTA(Time In column: Time Out column)=2

    to Uncheck or show that the seat is Empty

    ***************************

    Unfortunately, we don't know the layout of your worksheet.

    To give you the right or more accurate solution to our problem please, 

    provide us with a sample of your workbook (with no confidential or sensitive data),

    Upload the file to OneDrive, DropBox, Google Drive, etc and share the link to the file here.

    And give more details of your scenario ad your goals

    Regards

    Jeovany

    Was this answer helpful?

    0 comments
  2. Anonymous
    2020-04-17T02:13:53+00:00

    Hi, 

    This is how it looks like right now :P It is very simple. It goes from 1 to 135 and then starts at 1 right after 135. We have three meal times. 

    Was this answer helpful?

    0 comments
  3. Anonymous
    2020-04-17T05:15:54+00:00

    Thanks for the information

    What formula or method you use to assign the seats to the employees once they scan their badges.?

    I clearly understand the VLOOKUP part 

    Could you explain us what do you mean by

    "The issue we have is that, sometimes it assigns a seat that is still taken. " ?

    Was this answer helpful?

    0 comments
  4. Anonymous
    2020-04-17T05:22:56+00:00

    Hi Debbie,

    I think it’s better to create a helper columns as mentioned by Jeovany and make a condition is such a way that whenever the employee scans the badge again, it will show up the seat is free to occupy.

    Here is the sample we think about:

     

    If I misunderstood your requirement, apologies for it and can you please provide some more detailed description of your requirement and share us the sample file by uploading to OneDrive to share the link to access it.

    Best Regards,

    Chitrahaas

    Was this answer helpful?

    0 comments