Loop through sheet, check if previous value is the same and do calculation

I have a spreadsheet with 3 columns and x number of rows. The headers look like: Description, Start Date, End Date. I need to loop through the rows and calculate the time difference for each one. If the description is the same as the previous row then ignore it. If it's different I need to calculate the total time difference.

Example:
Desc 1 1/26/2012 14:53:02 1/26/2012 14:53:11
Desc 1 1/26/2012 14:53:02 1/26/2012 14:53:11
Desc 2 1/26/2012 12:55:00 1/26/2012 12:55:09
Desc 2 1/26/2012 12:55:00 1/26/2012 12:55:09
Desc 2 1/26/2012 10:24:30 1/26/2012 10:24:48
Desc 2 1/26/2012 10:24:30 1/26/2012 10:24:48

I need the results to be entered in a new worksheet to look something like:
Description Duration Occurrences
Desc 1 00:09.0 2
Desc 2 00:27.0 4

If the times are the same with the same description then they are ignored. I have no idea where to being. I am a PHP developer so if I can get some help getting started I can probably figure it out.

Hi,

I think the following code should get you going:

Please Login or Register  to view this content.

I've added comments, so it should be self-explanatory. If not, let me know.

Cheers,
Paul

Wow this is awesome. This is exactly what I was looking for! I modified it slightly to ignore the same dates and not to count occurrences (which are really dupes so they don't need be counted)

Please Login or Register  to view this content.

I do have a question though. It works flawlessly for the whole sheet except for the first time in the loop. I get some weird results:


Desc1 1/0/1900 0:00:09 1/1/1900 0:00:00
Desc2 00:27.0 2
Desc3 00:21.0 2
Desc4 03:51.0 35
Desc5 00:27.0 2
Desc6 05:04.0 1


Desc1 should be 9 seconds with 2 occurrences. I will continue to debug this a little but there may be something I'm over looking.

I think the bug is that you're using Offset(i-1, 2), but first time through i=0, so the resulting negative offset was throwing off the initial result.

In your original message you said (and I missed this first time through, sorry about that) to ignore rows that have the same description (column A) and the same times (columns B and C). Given that, here's a revised version that does this and, in my testing, produces the correct results:

Please Login or Register  to view this content.

Cheers,
Paul