Problem matching columns and calculating a time difference. For Pros.

[maradri]

Hi. I'm sorry about the title that might confuse, but since you're reading this you have found it.

I have already spent several hours trying to solve this following problem, but haven't advanced anywhere nearer to a solution.

I have a spreadsheet containing the following columns:

Column A contains many rows with timestamps from a stopwatch.
Column B contains for every timestamp a number from 1 to 6 (the action that took place at this particular time).

I want a formula with the following properties:

The time that has passed between an event "1" and an event "2".

Difficulties:

"2" always follows "1", but not necessarily directly after each other. So you could say "1" is the start and "2" is the end, but there might be things in between that don't matter.
There is more than one pair of "1" and "2" in Column B.

I have no idea how to do this and I appreciate any help!!

Thank you very much and good luck!

Marius.

I'm happy to answer any questions that might arise.

[tigeravatar]

Marius,

Welcome to the forum!
To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.
To attach a file, click the "Go Advanced" button and then click the paperclip icon to open the Manage Attachments dialog.

[maradri]

Thanks tigeravatar.

For all those keen to solve my problem, here is a dummy-workbook how my data looks like.dummy-workbook.xlsx

Marius.

[tigeravatar]

Marius,

Attached is a modified version of your example file.
Row 1 is a header row
Actual data starts in row 2
Column D is the list of Event "1" times
Column E is the list of Event "2" times
Column F is their differences

In cell D2 and copied down to D31 is this formula (to get the list of Event "1" times):

Please Login or Register  to view this content.

In cell E2 and copied down to E31 is this formula (to get the list of Event "2" times):

Please Login or Register  to view this content.

In cell F2 and copied down to F31 is this formula (to get their differences):

Please Login or Register  to view this content.

Cells D2:F31 are formatted as [h]:mm:ss
Does that work for you?

[maradri]

Hats off to tigeravatar!
Thank you. Yes, that works. Elegant solution, although I admittedly don't understand half of how the formulas work, but they do and I can live with that at this moment in time.
Thanks again and all the best.
Marius.