Column Change

[vmjan02]

I have this data and not sure if there is a way to get the output.

eg:- Row no 3 have a name and with respect to row 4 the nex 5 rows has its left and right, so in single line with respect ot row 3 ti will have data for 4 and 5.
so in singel ine row 3 will have its all detils,

I tried differnt ways but no help any way to fix it a vba will do.


I am confused how to explain it but I hope this table will give the right view.

Book1
	B	C	D	E	F	G	H
1
2				Output
3	Bundle-Ether10			Gi101/0/0/4 Local Configured 0x8000	Gi102/0/0/4 Local Active 0x8000
4	Gi101/0/0/4 Local Configured 0x8000	0x0000 1000000
5	Gi102/0/0/4 Local Active 0x8000	0x0000 1000000
6	Bundle-Ether20			Gi100/0/0/5 Local Configured 0x8000	Gi101/0/0/5 Local Configured 0x8000
7	Gi100/0/0/5 Local Configured 0x8000	0x0000 1000000
8	Gi101/0/0/5 Local Configured 0x8000	0x0000 1000000
9	Bundle-Ether1001
10	Te0/2/0/28 Local Active 0x8000	0x0000 10000000		Te0/2/0/28 Local Active 0x8000	Te0/2/0/29 Local Active 0x8000	Te0/3/0/28 Local Active 0x8000	Te0/3/0/29 Local Active 0x8000
11	Te0/2/0/29 Local Active 0x8000	0x0000 10000000
12	Te0/3/0/28 Local Active 0x8000	0x0000 10000000
13	Te0/3/0/29 Local Active 0x8000	0x0000 10000000
14	Bundle-Ether1002			Te0/2/0/38 Local Active 0x8000	Te0/2/0/39 Local Active 0x8000	Te0/3/0/38 Local Active 0x8000	Te0/3/0/39 Local Active 0x8000
15	Te0/2/0/38 Local Active 0x8000	0x0000 10000000
16	Te0/2/0/39 Local Active 0x8000	0x0000 10000000
17	Te0/3/0/38 Local Active 0x8000	0x0000 10000000
18	Te0/3/0/39 Local Active 0x8000	0x0000 10000000
Sheet1

 

[vmjan02]

Once I run it its givne me this not sure why.

 

[sxhall]

Just noticed you have a space at the start of the text in the cells so it was producing a blank as an answer in the code, below corrects it and removes the spaces.


Two options as the above post shows data in column A as well...

• Option 1 - this will clear the data from column A when clearing data in columns B and C
• Option 2 - this will leave the data in column A and only clear data in column B and C

Option 1...

Sub MoveStuffOpt1()

Dim MatchRow As Integer
Dim MoveOffset As Integer
Dim ColLetter As String
Dim CelText As String
Dim Frow As Integer
Dim Lrow As Integer
Dim n As Long

Frow = 3        '<<< Change this number depending on the row your data starts in
ColLetter = "E" '<<< Change this letter to the column you want to start adding the data in to

Lrow = Range("B" & Frow).End(xlDown).Row 'sets the last row in the data assuming there are no gaps in the data
MoveOffset = 0

For Each i In Range("B" & Frow & ":B" & Lrow)
    If i.Value Like "Bundle*" Then
    MatchRow = i.Row
    MoveOffset = 0
    Else
        n = InStr(CelText, "Local") - 1
        Range(ColLetter & MatchRow).Offset(0, MoveOffset).Value = Left(CelText, n)
        MoveOffset = MoveOffset + 1
        i.EntireRow.ClearContents
    End If
Next i

End Sub

Option 2...

Sub MoveStuffOpt2()

Dim MatchRow As Integer
Dim MoveOffset As Integer
Dim ColLetter As String
Dim CelText As String
Dim Frow As Integer
Dim Lrow As Integer
Dim n As Long

Frow = 3        '<<< Change this number depending on the row your data starts in
ColLetter = "E" '<<< Change this letter to the column you want to start adding the data in to

Lrow = Range("B" & Frow).End(xlDown).Row 'sets the last row in the data assuming there are no gaps in the data
MoveOffset = 0

For Each i In Range("B" & Frow & ":B" & Lrow)
    If i.Value Like "Bundle*" Then
    MatchRow = i.Row
    MoveOffset = 0
    Else
        CelText = Trim(Replace(i.Value, " ", ""))
        n = InStr(CelText, "Local") - 1
        Range(ColLetter & MatchRow).Offset(0, MoveOffset).Value = Left(CelText, n)
        MoveOffset = MoveOffset + 1
        i.Resize(1, 2).ClearContents
    End If
Next i

End Sub

 

[vmjan02]

Option 2 is all perfect this is what is need , thanks a ton

 

[sxhall]

Option 2 is all perfect this is what is need , thanks a ton

No problem, got there in the end