Rearrange Data to CSV

[Red Fish]

I am working on a networking project and I need to output a CSV file for a database consumption. I am getting a list of computer hostnames, interface names, IPs and MACAddresses. Unfortunately they are not formatted the best and I need to rearrange the data to produce a CSV file. My output lists the hostname and the interface name and then lists hostname and IP/MAC as listed below. Some machines have more interfaces than others so I can't just count down and copy that to a new cell. The order of interface name will match up to the IPs so I just need to figure out how to merge lines. My ideal output would be hostname, Interface Name, IP, MAC. I am just not sure how to go about rearrange the data. Any suggestions?

computer1,InterfaceName 1
computer1,InterfaceName 2
computer1,InterfaceName 3
computer1,InterfaceName 4
computer1,{"127.0.0.1"} 00:00:00:00:00:01
computer1,{"127.0.0.2"} 00:00:00:00:00:02
computer1,{"127.0.0.3"} 00:00:00:00:00:03
computer1,{"127.0.0.4"} 00:00:00:00:00:04
computer2,InterfaceName 1
computer2,InterfaceName 2
computer2,{"127.0.0.1"} 00:00:00:00:00:01
computer2,{"127.0.0.2"} 00:00:00:00:00:02

I hope to get something like this.

computer1,InterfaceName 1,127.0.0.1, 00:00:00:00:00:01
computer1,InterfaceName 2,127.0.0.2, 00:00:00:00:00:02
computer1,InterfaceName 3,127.0.0.3, 00:00:00:00:00:03
computer1,InterfaceName 4,127.0.0.4, 00:00:00:00:00:04
computer2,InterfaceName 1,127.0.0.1, 00:00:00:00:00:01
computer2,InterfaceName 2,127.0.0.2, 00:00:00:00:00:02

 

[onlyadrafter]

Hello,

not the most elegant solution, but works for the sample data suppled

Sub COMBINE_LINES()
    Application.ScreenUpdating = False
    For MY_ROWS = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
        If Len(Range("A" & MY_ROWS).Value) < 42 Then
        MY_TEXT = Mid(Range("A" & MY_ROWS).Value, 11, Len(Range("A" & MY_ROWS).Value))
        For MY_NEW_ROWS = MY_ROWS - 1 To 1 Step -1
            If Len(Range("A" & MY_NEW_ROWS).Value) = 25 Then
                If Mid(Range("A" & MY_NEW_ROWS).Value, Len(Range("A" & MY_NEW_ROWS).Value) - 2, 1) <> ":" Then
                    Range("A" & MY_NEW_ROWS).Value = Range("A" & MY_NEW_ROWS).Value & "," & MY_TEXT
                    MY_TEXT = ""
                    Rows(MY_ROWS).Delete
                    GoTo CONT
                End If
            End If
        Next MY_NEW_ROWS
        End If
CONT:
      Next MY_ROWS
      Application.ScreenUpdating = True
End Sub

 

[VBA Geek]

Sub Prep_For_CSV()
Dim rng As Excel.Range, X As Long
Application.ScreenUpdating = False
With Range("B2:B" & CStr(Range("A" & Rows.Count).End(xlUp).Row))
    .Formula = "=IF(COUNTIF(A$1:A1,""* ??:??:??:??:??:??"")=COUNTIF(A$1:A2,""* ??:??:??:??:??:??""),"""",""#NAME?"")"
    .Value = .Value
    For Each rng In .SpecialCells(xlCellTypeConstants, xlErrors).Areas
        rng.Offset(, -1).Copy .Worksheet.Range("B2").Offset(X * 2)
        X = X + rng.Rows.Count
    Next rng
    .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
End With
With Range("C2:C" & Range("b" & Rows.Count).End(xlUp).Row)
    .Formula = "=A2&MID(B2,FIND("","",B2),FIND("" "",B2,FIND("","",B2))-FIND("","",B2))&"", ""&RIGHT(B2,17)"
    .Value = .Value
End With
End Sub

START:

Excel 2013
	A
1	Data
2	computer1,InterfaceName 1
3	computer1,InterfaceName 2
4	computer1,InterfaceName 3
5	computer1,InterfaceName 4
6	computer1,{"127.0.0.1"} 00:00:00:00:00:01
7	computer1,{"127.0.0.2"} 00:00:00:00:00:02
8	computer1,{"127.0.0.3"} 00:00:00:00:00:03
9	computer1,{"127.0.0.4"} 00:00:00:00:00:04
10	computer2,InterfaceName 1
11	computer2,InterfaceName 2
12	computer2,{"127.0.0.1"} 00:00:00:00:00:01
13	computer2,{"127.0.0.2"} 00:00:00:00:00:02
Sheet1

END (in column C the data that you want):

Excel 2013
	A	B	C
1	Data
2	computer1,InterfaceName 1	computer1,{"127.0.0.1"} 00:00:00:00:00:01	computer1,InterfaceName 1,{"127.0.0.1"}, 00:00:00:00:00:01
3	computer1,InterfaceName 2	computer1,{"127.0.0.2"} 00:00:00:00:00:02	computer1,InterfaceName 2,{"127.0.0.2"}, 00:00:00:00:00:02
4	computer1,InterfaceName 3	computer1,{"127.0.0.3"} 00:00:00:00:00:03	computer1,InterfaceName 3,{"127.0.0.3"}, 00:00:00:00:00:03
5	computer1,InterfaceName 4	computer1,{"127.0.0.4"} 00:00:00:00:00:04	computer1,InterfaceName 4,{"127.0.0.4"}, 00:00:00:00:00:04
6	computer2,InterfaceName 1	computer2,{"127.0.0.1"} 00:00:00:00:00:01	computer2,InterfaceName 1,{"127.0.0.1"}, 00:00:00:00:00:01
7	computer2,InterfaceName 2	computer2,{"127.0.0.2"} 00:00:00:00:00:02	computer2,InterfaceName 2,{"127.0.0.2"}, 00:00:00:00:00:02
Sheet1

 

[Red Fish]

Thanks for the response guys. I had been messing with this idea. Rather than try to parse through each item to check values, since I found some entries have ivp6 stuff, so to make it easy I looked for the { character and moved over to the next column using this code.

'Gather last row
iRow = Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row


'split column in two, A: Hostnames,Interface B:IP,Mac
Range("A1").Select
For i = iRow To 1 Step -1
If InStr(Cells(i, 1), "{") Then
Cells(i, 2).Value = Cells(i, 1).Value
Cells(i, 1).ClearContents
End If
Next

Works good up to the this point. I am using this to delete the empty cells and move up, but at some point it seems to go south. Perhaps there is a better way to delete the empty cells and move up. Still working on that.

Range("A1").Select
iRow = Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row


'delete blank spaces and move up
Range(Cells(1, 1), Cells(iRow, 2)).SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp

 

[VBA Geek]

hi - did you try both suggestions to see if they do what you ask for?

Thanks for the response guys. I had been messing with this idea. Rather than try to parse through each item to check values, since I found some entries have ivp6 stuff, so to make it easy I looked for the { character and moved over to the next column using this code.

'Gather last row
iRow = Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row


'split column in two, A: Hostnames,Interface B:IP,Mac
Range("A1").Select
For i = iRow To 1 Step -1
If InStr(Cells(i, 1), "{") Then
Cells(i, 2).Value = Cells(i, 1).Value
Cells(i, 1).ClearContents
End If
Next

Works good up to the this point. I am using this to delete the empty cells and move up, but at some point it seems to go south. Perhaps there is a better way to delete the empty cells and move up. Still working on that.

Range("A1").Select
iRow = Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row


'delete blank spaces and move up
Range(Cells(1, 1), Cells(iRow, 2)).SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp