Advanced Userform/ Input box

[PartyAlly]

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:

=IF(B2<>"",IF(C2<>"",C2,NOW()),"")

[TABLE="width: 349"]
<tbody>[TR]
[TD]Number of products[/TD]
[TD]Ordernumber[/TD]
[TD]Timestamp[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1100[/TD]
[TD="align: right"]10:12:21[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]10:14:08[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1100[/TD]
[TD="align: right"]10:14:11[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]10:14:20[/TD]
[/TR]
</tbody>[/TABLE]

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.
[TABLE="width: 525"]
<tbody>[TR]
[TD]Number of products[/TD]
[TD]Ordernumber[/TD]
[TD]Ordernumber 2nd scan[/TD]
[TD] Time in[/TD]
[TD] Time out[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1300[/TD]
[TD="align: right"]1300[/TD]
[TD="align: right"]10:04:48[/TD]
[TD="align: right"]10:26:38[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[nikio8]

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]

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.

[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.

[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.