Scan barcode to excel with date & time stamp in & out.

[qpywsqp]

Hi all. I have research around the internet and finally found out Macro able to solve my issue. But im totally not a programmer. Hopefully someone can help me on this.

I will scan QR Code with a scanner to cell A1 and wanted cell C1 to have date and time stamp for IN. When Scanner scan the same QR Code, D1 will have date and time stamp for out. If repeat third time scanning the same QR code after a few scanning, it will appear on next row. Which means A8 with the code and C8 with date and time stamp for IN again.

Is that possible to done with Macro?

 

[theburrow]

https://www.dropbox.com/s/l7rbdvvcc6uib5s/ACCESS LOG.xlsm?dl=0

 

[hiker95]

https://www.dropbox.com/s/l7rbdvvcc6uib5s/ACCESS LOG.xlsm?dl=0

theburrow,

I have had problems in the past when attempting to download an Excel file with macros, with the xlsm file extension.

Please rename the workbook using the xlsx file extension, and, remove all macros, and, then repost on dropbox.

 

[theburrow]

theburrow,

I have had problems in the past when attempting to download an Excel file with macros, with the xlsm file extension.

Please rename the workbook using the xlsx file extension, and, remove all macros, and, then repost on dropbox.

https://www.dropbox.com/s/qostp5r4g4xh4ff/ACCESS LOG.xlsx?dl=0

 

[hiker95]

theburrow,

When I attempted to download/open your workbook 'ACCESS LOG.xlsx' I received the following message:

Microsoft Office Excel
Circular Regerence Warning

One or more formulas contain a circular reference and may not calculate correctly.......


Maybe someone else on MrExcel will be able to help you.


Please see Rule #12 at the next link on how to continue with your thread.

https://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[MadeFromMetal]

Hello,

Would it be possible to modify the code in Reply #18 to require a 2nd input in cell B for an order number? The code would then look up both the name and order number and apply the same logic to fill in the in/out portion? If no match between the two, it would add Name and Order to the first blank cell in Columns A and B respectively.

A finished table for the day might look like this.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Order[/TD]
[TD]Time Out[/TD]
[TD]Time In[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]092617-1[/TD]
[TD="align: right"]9/26/2017 11:32[/TD]
[TD="align: right"]9/26/2017 14:31[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]092617-2[/TD]
[TD="align: right"]9/26/2017 11:33[/TD]
[TD="align: right"]9/26/2017 14:45[/TD]
[/TR]
[TR]
[TD]Jake[/TD]
[TD]092617-3[/TD]
[TD="align: right"]9/26/2017 11:33[/TD]
[TD="align: right"]9/26/2017 12:55[/TD]
[/TR]
[TR]
[TD]Jeff[/TD]
[TD]092617-4[/TD]
[TD="align: right"]9/26/2017 11:34[/TD]
[TD="align: right"]9/26/2017 15:15[/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD]092617-3[/TD]
[TD="align: right"]9/26/2017 12:56[/TD]
[TD="align: right"]9/26/2017 14:50[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]092617-5[/TD]
[TD="align: right"]9/26/2017 14:32[/TD]
[TD="align: right"]9/26/2017 18:22[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]092617-6[/TD]
[TD="align: right"]9/26/2017 14:46[/TD]
[TD="align: right"]9/26/2017 18:44[/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD]092617-7[/TD]
[TD="align: right"]9/26/2017 14:51[/TD]
[TD="align: right"]9/26/2017 18:42[/TD]
[/TR]
[TR]
[TD]Jeff[/TD]
[TD]092617-8[/TD]
[TD="align: right"]9/26/2017 15:17[/TD]
[TD="align: right"]9/26/2017 16:41[/TD]
[/TR]
[TR]
[TD]Jason[/TD]
[TD]092617-9[/TD]
[TD="align: right"]9/26/2017 17:00[/TD]
[TD="align: right"]9/26/2017 21:02[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]092617-8[/TD]
[TD="align: right"]9/26/2017 17:00[/TD]
[TD="align: right"]9/26/2017 19:10[/TD]
[/TR]
[TR]
[TD]Jordan[/TD]
[TD]092617-10[/TD]
[TD="align: right"]9/26/2017 17:02[/TD]
[TD="align: right"]9/26/2017 21:22[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]092617-11[/TD]
[TD="align: right"]9/26/2017 19:15[/TD]
[TD="align: right"]9/26/2017 22:58[/TD]
[/TR]
</tbody>[/TABLE]


Thanks for any help!

 

[hiker95]

MadeFromMetal,

Welcome to the MrExcel forum.

The code would then look up both the name and order number

I assume that the list for name, and, number, are in another worksheet.

In order to continue, I would have to see your actual workbook, containing the two worksheets.

You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

 

[MadeFromMetal]

Hiker95,

No lookup in another worksheet, just within the same one.

The Names and Order Numbers will/may be different from day to day.

It should only look within the same worksheet for a Name-Order pair match.

If it does not find one, it adds the new Name-Order pair to cells A and B of the next available row, then adds a time stamp in column C of that row. Much how the code in Reply 18 of this thread looks for a Name Match in the same worksheet.

If it does find a match, that means there is already a time stamp in the Time Out column (column C) so it would add a time stamp into Time In (Column D) of the row that the Name-Order pair already exists.

This is for Windows 10, Excel 2016 if that helps.

Thanks again for your help.

 

[hiker95]

A finished table for the day might look like this.

MadeFromMetal,

Can we have another screenshot/flat text display, of what the original table looked like?

 

[MadeFromMetal]

There was no original table. The table would start blank with the headers, like so.
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Order[/TD]
[TD]Time Out[/TD]
[TD]Time In[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]


But the progression would look like this.

1. Jim scans his name[TABLE="class: grid, width: 350, align: center"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Order[/TD]
[TD]Time Out[/TD]
[TD]Time In[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

2. Jim scans the order number and a time stamp populates

[TABLE="class: grid, width: 350, align: center"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Order[/TD]
[TD]Time Out[/TD]
[TD]Time In[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]092617-1[/TD]
[TD="align: right"]9/26/2017 11:32[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]


3. John scans his name
[TABLE="class: grid, width: 350, align: center"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Order[/TD]
[TD]Time Out[/TD]
[TD]Time In[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]092617-1[/TD]
[TD="align: right"]9/26/2017 11:32[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

4. John scans his order
[TABLE="class: grid, width: 350, align: center"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Order[/TD]
[TD]Time Out[/TD]
[TD]Time In[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]092617-1[/TD]
[TD="align: right"]9/26/2017 11:32[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]092617-2[/TD]
[TD="align: right"]9/26/2017 11:33[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

5. Jim gets done with his order and turns it in so he scans his name.


[TABLE="class: grid, width: 350, align: center"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Order[/TD]
[TD]Time Out[/TD]
[TD]Time In[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]092617-1[/TD]
[TD="align: right"]9/26/2017 11:32[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]092617-2[/TD]
[TD="align: right"]9/26/2017 11:33[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]


6. Jim scans the order he is turning in, and the code recognizes the name/order pair in the cells above and adds a time stamp to column D, then deletes Jim's name from the cell.

[TABLE="class: grid, width: 400, align: center"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Order[/TD]
[TD]Time Out[/TD]
[TD]Time In[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]092617-1[/TD]
[TD="align: right"]9/26/2017 11:32[/TD]
[TD="align: right"]9/26/2017 14:31[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]092617-2[/TD]
[TD="align: right"]9/26/2017 11:33[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

7. Jim grabs another order and scans his name.
[TABLE="class: grid, width: 400, align: center"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Order[/TD]
[TD]Time Out[/TD]
[TD]Time In[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]092617-1[/TD]
[TD="align: right"]9/26/2017 11:32[/TD]
[TD="align: right"]9/26/2017 14:31[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]092617-2[/TD]
[TD="align: right"]9/26/2017 11:33[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]



8. Then Jim scans the New order. The code does not see a name/order pair match in the cells above so it adds a time stamp into column C.

[TABLE="class: grid, width: 400, align: center"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Order[/TD]
[TD]Time Out[/TD]
[TD]Time In[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]092617-1[/TD]
[TD="align: right"]9/26/2017 11:32[/TD]
[TD="align: right"]9/26/2017 14:31[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]092617-2[/TD]
[TD="align: right"]9/26/2017 11:33[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]092617-3[/TD]
[TD="align: right"]9/26/2017 14:32[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]



That would be the end of the code. It would just treat every pair of scans with the same logic as above. I hope this makes sense.

 

[hiker95]

MadeFromMetal,

I have attempted several times to understand your work progression, but, I can not see the logic to solve your request.

Maybe someone else on MrExcel will be able to help you.