How to identify offset data and execute multiple formulas

[davensocal]

Hello All-
Sorry to ask this of everyone, but I am not that good at Excel 2003, and I
am getting a headache reading through all the posts..

What I want to do is determine the amount of time an item spends in certain
parts of a machine, as well as the total time spent in the machine. The
machine log file is recording the in and out times.

I believe I need to do a Vlookup to find the locations of interest and pull
the data from the relative cell. I am not sure how to configure Vlookup to
handle multiple returns.

After I get past that hurtle, I also need to find a way to pull the Item ID
number as well..

Here is a sample of the file..
I can easily find how long an item spent in a location by subtracting the in
time from the out time (column E from Column F).
I would like to subtract the time each slot left Unit 2 from when it entered
Unit 3 to determine the total time spent in the machine. The max number of
slots is 25, but the number of units an item could visit is dynamic. The
ouptut should also identify which slot the item came from..

Any help would be appreciated..

A B C D E F
Slot ID Unit Name In Out
1 3 15:21:51.437 15:21:53.468
1 6 15:21:55.437 15:21:57.359
1 15 15:22:19.312 15:23:27.328
1 11 15:23:31.484 15:24:18.187
1 24 15:24:21.218 15:24:22.984
1 29 15:24:26.031 15:25:13.203
1 26 15:25:17.453 15:26:25.234
1 33 15:26:29.593 15:28:32.281
1 31 15:28:36.125 15:29:23.000
1 40 15:29:26.000 15:29:27.906
1 52 15:29:30.234 15:29:32.156
1 66 15:29:34.656 15:29:36.609
1 72 15:29:40.015 15:31:30.765
1 65 15:31:34.156 15:31:36.203
1 51 15:31:38.765 15:31:40.703
1 47 15:31:43.734 15:32:31.328
1 42 15:32:35.546 15:34:36.328
1 39 15:34:38.828 15:34:41.109
1 23 15:34:43.156 15:34:47.406
1 5 15:34:49.421 15:34:51.453
1 86 15:34:55.156 15:35:49.406
1 2 15:35:53.593 15:35:56.562
1 Complete
2 3 15:22:01.765 15:22:03.718
2 6 15:22:06.031 15:22:21.265
2 16 15:22:25.140 15:23:35.515
2 12 15:23:39.671 15:24:39.171
2 24 15:24:42.156 15:24:44.078
2 30 15:24:47.156 15:25:34.140
2 27 15:25:38.312 15:26:52.343
2 34 15:26:56.765 15:28:53.218
2 32 15:28:57.015 15:29:44.578
2 40 15:29:47.562 15:29:49.500
2 52 15:29:51.828 15:29:53.843
2 66 15:29:56.312 15:29:58.421
2 74 15:30:01.687 15:31:52.453
2 65 15:31:55.593 15:32:01.406
2 51 15:32:03.984 15:32:06.031
2 48 15:32:09.031 15:32:56.156
2 43 15:33:00.375 15:34:53.375
2 39 15:34:55.843 15:35:05.875
2 23 15:35:08.015 15:35:10.031
2 5 15:35:12.281 15:35:14.437
2 15:35:16.984 15:35:45.156
2 86 15:35:51.015 15:36:42.968
2 2 15:36:47.328 15:36:50.203
2 Complete
3 3 15:22:12.218 15:22:22.046
3 6 15:22:24.328 15:22:27.265
3 17 15:22:31.000 15:24:11.656
3 11 15:24:19.218 15:25:06.140
3 24 15:25:09.187 15:25:11.125
3 29 15:25:14.203 15:26:01.343
3 28 15:26:05.828 15:27:13.562
3 35 15:27:17.890 15:29:20.187
3 31 15:29:24.187 15:30:11.531
3 40 15:30:14.546 15:30:16.437
3 52 15:30:18.796 15:30:20.875
3 66 15:30:23.375 15:30:25.406
3 75 15:30:28.875 15:32:19.562
3 65 15:32:22.562 15:32:24.640
3 51 15:32:27.218 15:32:29.296
3 47 15:32:32.312 15:33:28.921
3 44 15:33:32.312 15:35:29.671
3 39 15:35:32.078 15:35:34.015
3 23 15:35:36.281 15:35:38.515
3 5 15:35:40.515 15:35:42.515
3 15:35:46.828 15:36:40.156
3 86 15:36:44.625 15:37:27.984
3 2 15:37:32.203 15:37:35.156
3 Complete

 

[Sheeloo]

Send the file to me... I will enter the formulae and send back to you...
Trying to copy what I have done here (partial data);

If you sum Col G and H for one slot id you will get the total time in the
machine...
Col I gives you the time in an unit... you will need to insert the missing
values to have a generic solution...

A B C D E F G H I
New Column Slot Id Unit Name In Out Time in a slot Time between Slots Slot
ID&UnitName
=B3&C3 1 3 21:51.4 21:53.5 =E3-D3 =D4-E3 =B3&C3 =VLOOKUP(H3,A:F,6,FALSE)
=B22&C22 1 5 34:49.4 34:51.5 =E22-D22 =D23-E22 =B22&C22 =VLOOKUP(H22,A:F,6,FALSE)
=B23&C23 1 86 34:55.2 35:49.4 =E23-D23 =D24-E23 =B23&C23 =VLOOKUP(H23,A:F,6,FALSE)
=B24&C24 1 2 35:53.6 35:56.6 =E24-D24
=B25&C25 1 Complete Complete