Calculating Node aging using MACRO

[chaitanya_gowtham]

Dear Team,

We are getting raw data with node down time and uptime. And we need to calculate the down time using the data got from server.

Manually we are arranging the data with selected time and sorting the data and using some formulas we are arranging the report( Sample attached with name Time Duration After arranging)

But now we attached the raw data also( Time Duration Before arranging). Can any one help us to get the macro for arrange and time calculation.

Regards,
Chaitanya Gowtham A

[JeteMc]

Hello chaitanya_gowtham and Welcome to Excel Forum.
As this query has gone unanswered for several days I am going to make a formula based proposal (which may lead someone to write VBA code).
This proposal employs numerous helper columns which may be hidden for aesthetic purposes. The helper columns (I:L) first match down/up and down time/uptime values based on my understanding of the 'After' arrangement.
The helper columns next set up a ranking in columns M:P so that the output in columns Q:V resembles the 'After' arrangement in the file attached to post #1.
Note that some of the data in the 'After' arrangement is not present in the 'Before' arrangement.
Two formula changes of note are the ggg column: =IF(Q12="","",T12=Q12)
and the Net Down Time column: =IF(Q12="","",U12-R12)
Let us know if you have any questions.

[chaitanya_gowtham]

Dear JeteMc,

Thanks for the formula and I am unable to copy the formula after I20 and so on. Formula is working from I12 to I19 only. Can you help us or enable this formula in reset of I column.

Regards,
Chaitanya Gowtham A

[JeteMc]

In the attached copy of the file the ranges of the formulas that populate columns I:V have been extended through row 100.
One formula (column N) is changed to read: =IF(J12="","",SUMPRODUCT((M$12:M$100<M12)*(M$12:M$100<>""))+1)
If the data extends further down the sheet the formulas may be modified to fit i.e. if there are 200 lines of data modify the formula in column N to read: =IF(J12="","",SUMPRODUCT((M$12:M$250<M12)*(M$12:M$250<>""))+1)
Let us know if you have any questions.