TIMESTAMP with MIN AND MAX TIMES

[kr0nic]

Hello everyone! I am new to the forums and this is my first post so please be kind.

My excel version is 2010.


I have 3 operators using a barcode scanner to scan products coming into the factory. They stay in the factory for a period of time and then they are scanned out again.

I require to know the time when a product was scanned in and out.

The problem is that some operators may scan the product multiple times by accident. Therefore I have structured my excel sheet like this:

column A - will be the scan input by operators for both scan in and out times.
column B - will be a timestamp for each scan in column A (I need help on a formula or VBA for this)
column C - currently I am using this formula, is this the best formula to use? {=IFERROR(INDEX($C$2:$C$199,MATCH(0,COUNTIF($A$2:A2,$C$2:$C$400),0)),"")}

column D - will choose the minimum time from the list of C. (I need help on a formula or VBA for this)
column E - will choose the maximum time from the list of C. (I need help on a formula or VBA for this)
column F - will find the time difference between D and E in minutes.



[TABLE="width: 995"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]column A[/TD]
[TD]column B[/TD]
[TD]column C[/TD]
[TD]column D[/TD]
[TD]column E[/TD]
[TD]column F[/TD]
[/TR]
[TR]
[TD]this is the barcode scan input [/TD]
[TD]timestamp of when the scan was inputted[/TD]
[TD]distinct unique list of column 'A'[/TD]
[TD](min time from list of B for person listed in C)[/TD]
[TD](max time from list of B for person listed in C)[/TD]
[TD]time difference between column D and E in minutes



[/TD]
[/TR]
</tbody>[/TABLE]


Sorry for the long post, but I am really stuck.

I have a sample worksheet if need be.

Kind regards
[TABLE="width: 995"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]



[/TD]
[/TR]
</tbody>[/TABLE]

 

[npatridge]

Before anything I admit I don't quite understand what Column C is about, but you here is a formula for column D:

=INDEX($A$1:$B$99,MATCH(A2,$A$1:$A$99,0),2)

and for Column E (which is an array formula, so enter it and confirm with CTRL + SHIFT + ENTER):

=INDEX($A$1:$B$99,(MAX(ROW($A$1:$A$99)*($A$1:$A$99=A2))),2)

and finally for column F:

=E2-D2

 

[Smitty]

Welcome to the Board!

How many scans are you talking about? I ask because you could easily overwhelm Excel, so before you invest a lot of development time Access might be a better idea.

 

[kr0nic]

Hi, thank you for your reply.

Column A will be the input for the product. i.e apples.
But the operator may scan apples 2-3 times, so column A will have apples listed 2-3 times.

Column C is to filter out the multiple entries in Column A and have just Apples and the first time it was scanned in .

If you have a better way to doing this, by all means please suggest!

Thank you.

 

[kr0nic]

Less than 200 scans per day. I prefer using excel as I am not familiar with access

 

[npatridge]

Column C is to filter out the multiple entries in Column A and have just Apples and the first time it was scanned in .

If you have a better way to doing this, by all means please suggest!

I would recommend a Pivot Table for that. Actually, now that I think about it, you may not need columns C, D, E or F. Try this:

Highlight columns A & B. Now got to Insert > Pivot Table. Choose where you'd like to put your pivot table (probably a new sheet). Now set your pivot table up like this:

Then, in E2 put =D2-C2 and drag it down. Done!

 

[kr0nic]

thank you! this works very well. do you have a quick way to making this pivot table auto refresh as soon as someone scans something into column A?