Very hard to transform table (Maybe with transpose formula ?)

[mexiro]

Hello Guys,

I have an excel output which pings some IP for several PC each hour. PCs at a given time are either online or offline. I need to gather data and prepare lists of PCs going offline, and later even make a chart of it. Most of the time PCs are online so what it matters is offline (disconnections)

data is as below:

[TABLE="width: 248"]
<tbody>[TR]
[TD] Time[/TD]
[TD]PC NAME[/TD]
[TD]Status:[/TD]
[/TR]
[TR]
[TD="align: right"]06:27:44[/TD]
[TD]PC1[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD="align: right"]06:27:44[/TD]
[TD]PC2[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD="align: right"]06:27:44[/TD]
[TD]PC3[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD="align: right"]06:27:44[/TD]
[TD]PC4[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD="align: right"]06:27:44[/TD]
[TD]PC5[/TD]
[TD]Offline[/TD]
[/TR]
[TR]
[TD="align: right"]07:27:44[/TD]
[TD]PC1[/TD]
[TD]Offline[/TD]
[/TR]
[TR]
[TD="align: right"]07:27:44[/TD]
[TD]PC2[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD="align: right"]07:27:44[/TD]
[TD]PC3[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD="align: right"]07:27:44[/TD]
[TD]PC4[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD="align: right"]07:27:44[/TD]
[TD]PC5[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD="align: right"]08:27:44[/TD]
[TD]PC1[/TD]
[TD]Offline[/TD]
[/TR]
[TR]
[TD="align: right"]08:27:44[/TD]
[TD]PC2[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD="align: right"]08:27:44[/TD]
[TD]PC3[/TD]
[TD]Offline[/TD]
[/TR]
[TR]
[TD="align: right"]08:27:44[/TD]
[TD]PC4[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD="align: right"]08:27:44[/TD]
[TD]PC5[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD="align: right"]09:27:44[/TD]
[TD]PC1[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD="align: right"]09:27:44[/TD]
[TD]PC2[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD="align: right"]09:27:44[/TD]
[TD]PC3[/TD]
[TD]Offline[/TD]
[/TR]
[TR]
[TD="align: right"]09:27:44[/TD]
[TD]PC4[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD="align: right"]09:27:44[/TD]
[TD]PC5[/TD]
[TD]Online[/TD]
[/TR]
</tbody>[/TABLE]


I need to gather data from the above to the below table:
[TABLE="width: 349"]
<tbody>[TR]
[TD]PC NAME[/TD]
[TD]Status:[/TD]
[TD]Time Offline[/TD]
[TD]Time back to online[/TD]
[/TR]
[TR]
[TD]PC5[/TD]
[TD]Offline[/TD]
[TD="align: right"]06:27:44 PM[/TD]
[TD="align: right"]07:27:44 PM[/TD]
[/TR]
[TR]
[TD]PC1[/TD]
[TD]Offline[/TD]
[TD="align: right"]07:27:44 PM[/TD]
[TD="align: right"]08:27:44 PM[/TD]
[/TR]
[TR]
[TD]PC3[/TD]
[TD]Offline[/TD]
[TD="align: right"]08:27:44 PM[/TD]
[TD]N/A#[/TD]
[/TR]
</tbody>[/TABLE]

According to the above I need to make a report on a PC how often disconnected and for how long. I also need to make a chart of it, but for the moment I need to gather the data in this way.

Any help?

thanks,

 

[JoeMo]

Assuming the data point for PC1 at 8:27:44 should be "Online" rather than the "Offline" your post shows, this seems to work.
The data are transformed in cols F:I as shown below:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Time	PC NAME	Status:			PC NAME	Status	Time Offline	Time back to online
2	6:27:44	PC1	Online			PC5	Offline	6:27:44 AM	7:27:44 AM
3	6:27:44	PC2	Online			PC1	Offline	7:27:44 AM	8:27:44 AM
4	6:27:44	PC3	Online			PC3	Offline	8:27:44 AM
5	6:27:44	PC4	Online
6	6:27:44	PC5	Offline
7	7:27:44	PC1	Offline
8	7:27:44	PC2	Online
9	7:27:44	PC3	Online
10	7:27:44	PC4	Online
11	7:27:44	PC5	Online
12	8:27:44	PC1	Online
13	8:27:44	PC2	Online
14	8:27:44	PC3	Offline
15	8:27:44	PC4	Online
16	8:27:44	PC5	Online
17	9:27:44	PC1	Online
18	9:27:44	PC2	Online
19	9:27:44	PC3	Offline
20	9:27:44	PC4	Online
21	9:27:44	PC5	Online
Sheet1

Sub mexiro()
Dim lR As Long, R As Range, vA As Variant, vB As Variant, vO() As Variant, nR _
    As Long, i As Long, j As Long, ct As Long
lR = Range("B" & Rows.Count).End(xlUp).Row
Set R = Range("A1:C" & lR)
vA = R.Value
Application.ScreenUpdating = False
Columns("F:I").ClearContents
With Range("F1:I1")
    .Value = Array("PC NAME", "Status", "Time Offline", "Time back to online")
    .Font.Bold = True
    .WrapText = True
End With
ReDim vO(1 To UBound(vA, 1), 1 To 4)
For i = LBound(vA, 1) To UBound(vA, 1)
    If vA(i, 3) = "Offline" Then
        If i < lR Then
            ct = ct + 1
            vO(ct, 1) = vA(i, 2)
            vO(ct, 2) = vA(i, 3)
            vO(ct, 3) = vA(i, 1)
            For j = i + 1 To UBound(vA, 1)
                If vA(j, 2) = vA(i, 2) And vA(j, 3) = "Online" Then
                    vO(ct, 4) = vA(j, 1)
                    Exit For
                End If
            Next j
        Else
            ct = ct + 1
            vO(ct, 1) = vA(i, 2)
            vO(ct, 2) = vA(i, 3)
            vO(ct, 3) = vA(i, 1)
            vO(ct, 4) = CVErr(xlErrNA)
            
        End If
    End If
Next i
nR = Range("F" & Rows.Count).End(xlUp).Row + 1
Range("F2:I" & ct).Value = vO
Columns("H:I").NumberFormat = "[$-F400]h:mm:ss AM/PM"
Columns("F:I").AutoFit
Application.ScreenUpdating = True
End Sub