Merge data if Column = Same

[jhayes]

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.

Please Login or Register  to view this content.

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]

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]

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

Please Login or Register  to view this content.

Please Login or Register  to view this content.

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

[Stuart Farr]

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]

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!

[Stuart Farr]

Glad to be of help.
Stu

[jhayes]

its great, it helps, but theres one more thing i am trying to do, and maybe its asking too much..

Using the formula you showed me, I still have to find where each shipment begins in a cell, =SUMIF(A1:A100,A3,C2:C100)-SUMIF(A1:A100,A3,B2:B100)

Out of the ... 765 records my report is generating, there is 185 different shipment #'s, and if possible, I would love to avoid fishing through them... and unfortunately, the extremely expensive inventory management software my company is using was not developed/customized correctly and in certain instances, the information you want just isn't obtainable is an easy fashion.


I've been thinking really hard on this, and a half-@$$ idea popped in my head, but i haven't the knowledge to fully understand how to do it.

If i had a seperate column, with only the shipment numbers, and no duplicates, so I would just have the 185 numbers, could I use some sort of "IF" formula to say... if A:A is equal to b5, then apply this same formula ; =SUMIF(A1:A100,A3,C2:C100)-SUMIF(A1:A100,A3,B2:B100)

if i could apply something like that to the entire column of cells, and get the totals that way...

[Stuart Farr]

Try using a pivot table, that should do it.

Stu