How to automate Time in & out; and detect double entries

[mhyven]

Hey Guys,

Please help me make a database wherein the time will be automatically shown once I input a data.
I have attached a file for reference..
I am using a bar code scanner, and sometimes an entry is being entered twice...

Say on the First scanning (Column C):
I scanned a barcode then it typed 123 in C2 then the time will automatically appear in D2
I scanned a barcode then it typed 456 in C3 then the time will automatically appear in D3
I scanned a barcode then it typed 789 in C4 then the time will automatically appear in D4
I scanned a barcode then it typed 321 in C5 then the time will automatically appear in D5
I scanned a barcode then it typed 654 in C6 then the time will automatically appear in D6
I scanned a barcode then it typed 987 in C7 then the time will automatically appear in D7
then I scanned a barcode then it typed 321 again, can excel detect double entries in the same column and make color fill the cell so I can immediately delete the double entries..

On the Second scanning (column G):
I scanned a barcode then it typed 321 in G2 then the time will automatically appear in E5 (I said E5 because it will serve as Time Out for 321 in C5 - like matching the first scan with the second scan)
likewise, i scanned another barcode the it typed 456 in G3 then the time will automatically appear E3
i scanned another barcode the it typed 789 in G4 then the time will automatically appear E4
i scanned another barcode the it typed 654 in G5 then the time will automatically appear E6
i scanned another barcode the it typed 987 in G6 then the time will automatically appear E7
i scanned another barcode the it typed 123 in G7 then the time will automatically appear E2
;

Also, please include this formula for column F: F=E-D for the total time lapsed (hh:mm:ss)..



Thank you.

[NoSparks]

Questions:

1.) How do you know or determine where the scanned s/n will be entered on the sheet ?

2.) What is the significance or purpose of the Control Number and where does it come from ?

3.) Are scan numbers a one-time only thing never to be used again ? Or would the date come into play for this ?

4.) Are your sample "In" and "Out" scans not matching on rows intentional ? Is column G really necessary ?

5.) What is the ultimate goal or use of this ?

[mhyven]

NoSparks,

This is the same as my previous post "is this possible in excel". The only difference is the designated columns.

Answers:

1.) when we received documents for processing, we will scan it in column C as time in. After it was processed, we will scan it again in Column G for the time out. Note, some documents needs more time to be processed that's why it is timed out at different time.

2.)Control Number is based on the first come first served basis, So if he's the first to file then he will be issued control number 1.

3.)It is one-time only thing. (for this, I will use the conditional formatting you've thought me on the other post)

4.) Yes, it is intentional because some documents takes longer time to be processed. Column G is necessary because it is where we will scan the "out" documents.

5.) To determine the exact time it is applied (time in - column D) and when it is processed (time out - column E) also, to determine the total time it takes to process a single document (column F).

[mhyven]

Is it possible for excel to do this?? - this is the title of the previous post..

Sorry..

[NoSparks]

A week or so back I added a second sheet to the file you posted.
Without knowing your answers to the questions I've asked, I went ahead and set it up for the answers I hoped to receive.

I think it's simpler than what you envision.

ExcelKey_Target Database for reference.xlsm

In a nut shell, scan everything into the same cell and let Excel deal with it.
Play around with entering new or existing items into the green cell.
Before changing it to match how you describe things, is this something you could use?

[mhyven]

NoSparks,

This exactly what we need. The "scan everything into the same cell" and the pop-up notice when it is already been scanned as in & out is just awesome. And YES, this is something that we will surely use.

Thank you very much.

1,048,576 thumbs up to you buddy!!

[mhyven]

NoSparks,

The attached database that you made was very useful. Yet again, we need your help.

Can you please make the database able to scan three times? The third scan being the series of control number (column C) from 1 to 1,048,576 (just an example).

Also, can you make the active cell (the cell where the new data is inputted) visible while freezing the panel where the "barcode scan --->" is? Say, the 59th scanned barcode was inputted in D56.. can the window automatically scroll to d56 while freezing the panel where the "barcode scan --->" is?

After which the 4th scan will make a pop-up remarks saying "scanned completely".

Thank you.

[NoSparks]

Why?
The very first time a particular barcode is scanned
--> the date goes into column A
--> the control number is added in column B
--> the barcode itself is written into column C
--> the current time is entered in column D

The next time that particular barcode is scanned (second scan)
--> the current time is entered in column E
--> the Lapsed time is calculated and entered into column F

The next time that particular barcode is scanned (third scan and all subsequent scans)
--> a message pops up displaying the barcode number and indicating it's already IN and OUT

Isn't this third scan already doing what you're asking?
Maybe just the message needs changed?

The scrolling of the sheet to see the row being dealt with... I'll look into.

[mhyven]

NoSparks,


There has been changes in procedure and it affected the code.

The very first time a particular barcode is scanned
--> the barcode itself is written into column C
--> the current time is entered in column D

The next time that particular barcode is scanned (second scan)
--> the current time is entered in column E
--> the Lapsed time is calculated and entered into column F

The next time that particular barcode is scanned (third scan)
--> the control number is entered in column B (numerically and ascending)

The next time that particular barcode is scanned (fourth scan and all subsequent scans)
--> a message pops up displaying the barcode number and indicating "process complete"

then the additional active cell will be shown in the window..

thank you.

[mhyven]

** "process completed"