New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Challenge: Extract timespand between events

Macros, VBA, Excel Automation, etc.

Challenge: Extract timespand between events

Postby gotyou2 » Mon Apr 11, 2016 1:35 pm

Hi All
I am rather new at this. I am stucked here.

I want to extract a timespand betwen events.
Column A = EventID
EventID that starts with 800 and end with 307

Column B = Logged time

Column C = JobID
JobID the job that has created the event
When JobID reaches 255 it starts over
There are at least two JobID with same name (Thats ok as they each gives an EventID)

I need a code that matches the two EventID (800 and 307) with the same JobID and gives me the timespand between them.
The code must be able to continue EventID 307 does not exist.

Have uploaded a picture of the data.

Please help

Thanks
  • 0

You do not have the required permissions to view the files attached to this post.
gotyou2
Rookie
 
Posts: 3
Joined: Apr 11, 2016
Reputation: 0
Excel Version: 2013

Re: Challenge: Extract timespand between events

Postby gotyou2 » Tue Apr 12, 2016 7:06 am

Hi I managede to do something that work for the first 239 row and then stops working.
Why?

Code: Select all
=IF(AND(COUNTIF(C2:C240;C1);(AND(COUNTIF(A2:A240;INDEX(A2:C240;MATCH(C1;C2:C240;0)+ROW(C2:C240)-1;1));(COUNTIF(C2:C240;C1)))));SUM(B1-(INDEX(A2:C240;(MATCH(C1;C2:C240;0)+ROW(C2:C240)-1);2)));"Has been calculated")


So I only get "Has been calculated" after row 239
  • 0

gotyou2
Rookie
 
Posts: 3
Joined: Apr 11, 2016
Reputation: 0
Excel Version: 2013

Re: Challenge: Extract timespand between events

Postby gotyou2 » Thu Apr 14, 2016 5:13 am

HI
Just talking to myself here?

Anyway solved it.
Code: Select all
=IF(AND(COUNTIF(H2:H239;H1);(AND(COUNTIF(F2:H240;INDEX(F2:H239;MATCH(H1;H2:H240;0)+1;1));(COUNTIF(H2:H240;H1)))));SUM(G1-(INDEX(G1:H240;MATCH(H1;H2:H240;0)+1;1)));"Has been calculated")


Lots of great findings in this forum though :)
  • 0

Last edited by pecoflyer on Thu Apr 14, 2016 5:39 am, edited 1 time in total.
Reason: added correct tags
gotyou2
Rookie
 
Posts: 3
Joined: Apr 11, 2016
Reputation: 0
Excel Version: 2013


Return to Macros and VBA Questions

Who is online

Users browsing this forum: No registered users and 39 guests