New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

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

Macros, VBA, Excel Automation, etc.

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

Postby mhyven » Sat Oct 10, 2015 12:42 am

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.
  • 0

You do not have the required permissions to view the files attached to this post.
mhyven
Regular
 
Posts: 29
Joined: Sep 21, 2015
Reputation: 0
Excel Version: 2013

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

Postby NoSparks » Sat Oct 10, 2015 6:04 pm

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 ?
  • 1

NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010

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

Postby mhyven » Mon Oct 19, 2015 5:01 am

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).
  • 0

mhyven
Regular
 
Posts: 29
Joined: Sep 21, 2015
Reputation: 0
Excel Version: 2013

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

Postby mhyven » Mon Oct 19, 2015 5:03 am

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

Sorry.. :)
  • 0

mhyven
Regular
 
Posts: 29
Joined: Sep 21, 2015
Reputation: 0
Excel Version: 2013

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

Postby NoSparks » Mon Oct 19, 2015 8:34 am

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?
  • 1

You do not have the required permissions to view the files attached to this post.
NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010

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

Postby mhyven » Wed Oct 21, 2015 12:08 pm

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!!

:D :D :D :D
  • 0

mhyven
Regular
 
Posts: 29
Joined: Sep 21, 2015
Reputation: 0
Excel Version: 2013

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

Postby mhyven » Mon Dec 07, 2015 10:28 am

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.
  • 0

mhyven
Regular
 
Posts: 29
Joined: Sep 21, 2015
Reputation: 0
Excel Version: 2013

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

Postby NoSparks » Mon Dec 07, 2015 12:59 pm

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.
  • 0

NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010

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

Postby mhyven » Tue Dec 08, 2015 7:57 am

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.
  • 0

mhyven
Regular
 
Posts: 29
Joined: Sep 21, 2015
Reputation: 0
Excel Version: 2013

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

Postby mhyven » Tue Dec 08, 2015 7:59 am

** "process completed"
  • 0

mhyven
Regular
 
Posts: 29
Joined: Sep 21, 2015
Reputation: 0
Excel Version: 2013

Next

Return to Macros and VBA Questions

Who is online

Users browsing this forum: Google [Bot] and 35 guests

cron