Advanced Userform/ Input box

PartyAlly

New Member
Joined
Aug 6, 2018
Messages
2
Hi!
Background: I work with customer service and want to register how long they have to wait before they get their products. I receive a receipt from each customer and then we are multiple people at once picking their orders.

How I have done thus far:
I use the "Forms" function to add information like the table below.
The ordernumber is a scanned barcode and contains a "Enter" press.
The timestamp is a function that looks like this:
Code:
=IF(B2<>"",IF(C2<>"",C2,NOW()),"")

Number of productsOrdernumberTimestamp
2110010:12:21
4120010:14:08
2110010:14:11
4120010:14:20

<tbody>
</tbody>

The data I extract is 1. Waitingtime depending on how many products and 2. Waitingtime depending on time of day.
The problems is that we don't do one customer at a time, so the ordernumber doesn't end up in a good order (like above.) This requires more post process then I would like.

To the question:
Is it possible for excel to recognize that the barcode has been scanned before and then add information in a new cell horizontally beside the first? I only want to enter "Number of products" and hit "Tab" once of twice.
Number of productsOrdernumberOrdernumber 2nd scan Time in Time out
21300130010:04:4810:26:38

<tbody>
</tbody>

Or is there a better solution to my problem? It has to be quick and don't require clicking around with a mouse.
Best Regards
PartyAlly
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

nikio8

Board Regular
Joined
Oct 20, 2017
Messages
128
Hi,
it seems you have all the data, just get unique order numbers and use few formulas


Ordernumber No of prods Start time End Tme Time taken Formated time Time per product
1100 4 10:12:21 AM 10:14:11 AM 0.001273148 00:01 50s 00:00 28s
1200 8 10:14:08 AM 10:14:20 AM 0.000138889 00:00 12s 00:00 2s


Ordernumber No of prods Start time End Tme Time taken Formated time taken Time per product

1100
=SUMIFS($A$2:$A$6,$B$2:$B$6,I3)
=MINIFS($C$3:$C$6,$B$3:$B$6,I3)
=MAXIFS($C$3:$C$6,$B$3:$B$6,I3)
=L3-K3
=TEXT(M3,"HH:MM_ss")&"s"
=TEXT(M3/J3,"HH:MM_ss")&"s"
 

PartyAlly

New Member
Joined
Aug 6, 2018
Messages
2
Hi,
it seems you have all the data, just get unique order numbers and use few formulas


Ordernumber No of prods Start time End Tme Time taken Formated time Time per product
1100 4 10:12:21 AM 10:14:11 AM 0.001273148 00:01 50s 00:00 28s
1200 8 10:14:08 AM 10:14:20 AM 0.000138889 00:00 12s 00:00 2s


Ordernumber No of prods Start time End Tme Time taken Formated time taken Time per product

1100
=SUMIFS($A$2:$A$6,$B$2:$B$6,I3)
=MINIFS($C$3:$C$6,$B$3:$B$6,I3)
=MAXIFS($C$3:$C$6,$B$3:$B$6,I3)
=L3-K3
=TEXT(M3,"HH:MM_ss")&"s"
=TEXT(M3/J3,"HH:MM_ss")&"s"

Hi nikio8, thanks for reply.
There are two timestamps because I scan both when they come to me and when they leave, thus the problem. The method you showed requires me to manually find "Time In" and "Time out" and place them horizontally. Met me show you more data.
Code:
[TABLE="width: 311"]
<tbody>[TR]
[TD="align: right"]1[/TD]
[TD]snvdj5[/TD]
[TD="align: right"]12:09:48[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]SNVA7Q[/TD]
[TD="align: right"]12:11:22[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]snvdj5[/TD]
[TD="align: right"]12:12:17[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]snvdj7[/TD]
[TD="align: right"]12:16:49[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]SNV8K3[/TD]
[TD="align: right"]12:19:52[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]snvdj7[/TD]
[TD="align: right"]12:21:29[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]SNV8K3[/TD]
[TD="align: right"]12:22:13[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]SNVA7Q[/TD]
[TD="align: right"]12:31:42[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]snvdj8[/TD]
[TD="align: right"]12:37:53[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]snvdj8[/TD]
[TD="align: right"]12:39:47[/TD]
[/TR]
</tbody>[/TABLE]
When done for the day I copy paste special, and sort the data with the variables to arrange them like this.
Code:
[TABLE="width: 195"]
<tbody>[TR]
[TD="align: right"]2[/TD]
[TD]SNV8K3[/TD]
[TD="align: right"]12:22:13[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]SNV8K3[/TD]
[TD="align: right"]12:19:52[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]SNVA7Q[/TD]
[TD="align: right"]12:31:42[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]SNVA7Q[/TD]
[TD="align: right"]12:11:22[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]snvdj5[/TD]
[TD="align: right"]12:12:17[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]snvdj5[/TD]
[TD="align: right"]12:09:48[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]snvdj7[/TD]
[TD="align: right"]12:21:29[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]snvdj7[/TD]
[TD="align: right"]12:16:49[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]snvdj8[/TD]
[TD="align: right"]12:39:47[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]snvdj8[/TD]
[TD="align: right"]12:37:53[/TD]
[/TR]
</tbody>[/TABLE]
Those steps are not a problem, what I'm looking for is a more efficient way for the input, so I dont have the arrange them later on.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,274
Messages
5,853,018
Members
431,540
Latest member
Hermione9908

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top