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.
<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>
Sorry for the long post, but I am really stuck.
I have a sample worksheet if need be.
Kind regards
<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>
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.
column A | column B | column C | column D | column E | column F |
this is the barcode scan input | timestamp of when the scan was inputted | distinct unique list of column 'A' | (min time from list of B for person listed in C) | (max time from list of B for person listed in C) | time difference between column D and E in minutes |
<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>
Sorry for the long post, but I am really stuck.
I have a sample worksheet if need be.
Kind regards
<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>