How to find the difference betwwen two rows based on conditions

[Thinker8]

Hi All,

I need to find the difference between two rows based on the two columns.

I have many columns in my sheet but few columns are going to be used for this.
tracking id, activity type and activity time are the columns.

I used to get this file from a reporting tool, which capture the activity timings of the tracking id.
i want to know how long the tracking id was in that particular activity type.

Activity types are open, accept/reject,pending confirmation, resolved, closed, etc.,

But for pending confirmation, the time taken would be calculated based on the second next row not the next row.If you see the attachment,will help you to know the exact requirement.


Thanks in Advance.tracking_id.xlsx

[RobertMika]

By find the difference do you mind to highlight both rows one with the "pending Confirmation" and the next one or to find the time between Pending and the second next based on ID?

[Thinker8]

Hi Robert,

yeah i want to find the time between pending and the second next based on ID.

[RobertMika]

Copy of tracking_id.xlsx
Please see attached.

[Thinker8]

Hi Robert,

I copied that formula in my original sheet, but its not working.

=IF(H3=$L$1,"",IFERROR(INDEX($I4:$I$100,MATCH(1,IF($C4:$C$100=C3,IF($H4:$H$100<>$L$1,1)),0))-I3,""))

i used the above formula. But i am getting #value for "$C4:$C$100=C3".

Column C contains tracking id.

[RobertMika]

Did you confirm the formula with Control+Shift+enter?
Not sure about your set up in original workbook so can not tell what in C3.Can you tell?
Could you post your workbook?

[Thinker8]

HI Robert,

Below the source sheet.

tracking_id_source.xlsx

[RobertMika]

You need to confirm with COntrol +Shift+Enter.
Sort by Id then by Date.
With 10000 rows the calculation will take a while.

[Thinker8]

Robert,
Its not working

[RobertMika]

Copy of tracking_id_source.xlsx
Try this

[Thinker8]

Thanks Robert,

Its working now..

[RobertMika]

You are welcome.