• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data cleanup from row to column

IKHAN

Member
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.
 

Attachments

p45cal

Well-Known Member
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.
 

Attachments

p45cal

Well-Known Member
This works on the active sheet and is even flakier then the Power Query offering.
Code:
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

New Member
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
 
Top