Merge data if Column = Same

**jhayes** · 04-03-2007, 09:16 AM

USING ~ Excel 2007
I am trying to get the average time of work completed using data from my company's software and exporting to excel.
I get about 800 lines of data per person.

Is there a way to make it so, if a range of say... A1:A4 is the same, then B1:B4 and C1:C4 can be added..and then C*-B* can occur?(and then A5:A7... etc. etc) So i can get the difference in time?
or if there is a simpler way to do it, That would be great... Currently, I have been taking the highest end time, and subtracting the lowest start time... but that is requiring me to go through everything manually.

Shipment          Start Time                 End Time
S5885	2007-03-05 09:23:00	2007-03-05 09:26:59
S5885	2007-03-05 09:26:59	2007-03-05 09:36:37
S5885	2007-03-05 09:36:37	2007-03-05 10:07:24
S5885	2007-03-05 10:07:24	2007-03-05 10:13:11
S5885	2007-03-05 10:13:11	2007-03-05 10:13:22
S5885	2007-03-05 10:13:21	2007-03-05 10:15:42
S5885	2007-03-05 10:15:41	2007-03-05 10:26:18
S5885	2007-03-05 10:26:18	2007-03-05 10:26:23
S5885	2007-03-05 10:26:23	2007-03-05 10:33:00
S5885	2007-03-05 10:33:00	2007-03-05 10:35:12
S5988	2007-03-05 13:09:37	2007-03-05 13:10:52
S5988	2007-03-05 13:10:52	2007-03-05 13:11:45
S5988	2007-03-05 13:11:45	2007-03-05 13:12:45
RTV61	2007-03-05 13:14:56	2007-03-05 13:15:42
RTV61	2007-03-05 13:15:42	2007-03-05 13:15:51
RTV61	2007-03-05 13:15:51	2007-03-05 13:16:05
RTV61	2007-03-05 13:16:05	2007-03-05 13:16:15
RTV61	2007-03-05 13:16:15	2007-03-05 13:16:23
RTV61	2007-03-05 13:16:15	2007-03-05 13:16:24

I am an excel newb

I tried =if(A:A=A:A,C:C-B:B,E5=000) but that just gives me each line individually...

Any help is greatly appreciated!!!

**Stuart Farr** · 04-03-2007, 12:29 PM

Hi,
There may be a smarter way but I simulated your example and by using SUMIF() based on criteria on col A, this achieved the result. i.e.
=SUMIF(A1:A100,A1,E1:E100)-SUMIF(A1:A100,A1,C1:C100).

You can of course change the criteria to whatever you need.

Stu

**jhayes** · 04-03-2007, 02:10 PM

Thanks for your help Stuart, but I was unsuccessful in getting it to work..

Shipment  Start         End
S5891	 07:44:29	 07:44:57
S5885	 07:52:24	 07:55:13
S5885	 07:55:12	 08:01:50
S5885	 08:01:50	 08:25:10
S5885	 08:25:10	 08:29:15
S5885	 08:29:15	 08:34:34
S5885	 08:34:34	 08:38:58
S5885	 08:38:57	 08:44:20
S5885	 08:44:20	 09:03:44
S5885	 09:03:44	 09:03:50
S5885	 09:03:50	 09:03:56
S5885	 09:12:44	 09:13:01
S5885	 09:23:00	 09:26:59
S5885	 09:26:59	 09:36:37
S5885	 09:36:37	 10:07:24
S5885	 10:07:24	 10:13:11
S5885	 10:13:11	 10:13:22
S5885	 10:13:21	 10:15:42
S5885	 10:15:41	 10:26:18
S5885	 10:26:18	 10:26:23
S5885	 10:26:23	 10:33:00
S5885	 10:33:00	 10:35:12
S5988	 13:09:37	 13:10:52
S5988	 13:10:52	 13:11:45
S5988	 13:11:45	 13:12:45
RTV61	 13:14:56	 13:15:42
RTV61	 13:15:42	 13:15:51
RTV61	 13:15:51	 13:16:05
RTV61	 13:16:05	 13:16:15

=SUMIF(A1:A100,A3,C2:C100)-SUMIF(A1:A100,A3,B2:B100)

That's what I used, but... no luck, I tried a few different variations... still nothing, I will keep trying.

**Stuart Farr** · 04-03-2007, 02:29 PM

When you say still nothing, what exactly do you mean? The Sumif() I gave should add all the end times for a given shipment and subtract the start times thereby giving total time per shipment. The formula assumes that the cells are formatted correctly i.e. time format and that we are not dealing with text strings. Can you confirm?

**jhayes** · 04-03-2007, 05:03 PM

I had a problem getting 0:00:00 value, but i removed the SPACE and i realized I was an idiot.

And, Stuart, after paying attention, I believe your formula is 100% what I need.

I need to check when i get a chance in the morning... but... In advance to it working, thank you!