Data cleanup from row to column

[IKHAN]

Hi
Need assistance with copied router data into excel for cleanup. Data is more than 1000 rows
a. Convert row data to separate columns
b. Delete row\data with certain text "Network Next Hop Metric LocPrf Weight Path"
c. Have highlighted scouple examples - delete row\text "Network Next Hop Metric LocPrf Weight Path" and move next row up after 2 spaces.

Attached sample file with 2 tabs (sample tab - copied from rtr) and (sample output file reqd.)

Thanks in advance.

 

[p45cal]

Power Query might be able to do this for you; see table at cell E1 of the Sample Output reqd sheet. If you change the data in the table on the other sheet, you'll need to refresh the green table by right-clicking it and choosing Refresh. You'll need to test this on more data.
If this data comes from a non-Excel file (such as a text file or a csv file) it'd be easier and more robust to do it direct from such files.

 

[IKHAN]

Thanks P45cal....Was looking for a vba macro to connect with other code

 

[p45cal]

This works on the active sheet and is even flakier then the Power Query offering.

Sub blah()
Dim result()
myVals = Range("A1").CurrentRegion.Columns(1)
ReDim result(1 To UBound(myVals), 1 To 3)
resultidx = 0
For i = 1 To UBound(myVals)
  If InStr(1, myVals(i, 1), "Network", vbTextCompare) = 0 Then
    Z = Mid(myVals(i, 1), 6)
    x = Application.Trim(Z)
    If Mid(Z, 2, 1) = " " Then x = " " & x
    xx = Split(x)
    Select Case UBound(xx)
      Case Is > 3
        resultidx = resultidx - (xx(0) <> "")
        If xx(0) <> "" Then result(resultidx, 1) = xx(0)
        result(resultidx, 2) = xx(1)
        result(resultidx, 3) = xx(3)
      Case 0
        resultidx = resultidx + 1
        result(resultidx, 1) = xx(0)
    End Select
  End If
Next i
'results are in array result(1 to resultidx,1 to 3), so say put them at cell C1:
Range("C1").Resize(resultidx, 3) = result
End Sub

 

[isabelle_foster]

Power Query might be able to do this for you; see table at cell E1 of the Sample Output reqd sheet. If you change the data in the table on the other sheet, you'll need to refresh the green table by right-clicking it and choosing Refresh. You'll need to test this on more data.
If this data comes from a non-Excel file (such as a text file or a csv file) it'd be easier and more robust to do it direct from such files.

Thank you for the useful and clear instructions. This task has always been difficult

 

[IKHAN]

Thank you @p45cal ...Macro works as expected.