converting rows data into columns on conditon Solved/Closed

Question

Hello, 

I have data which looks like below
ColA-----------ColB-------ColC--------ColD------------ColE---------ColF-------ColG----ColH--------
AA-------3-rt567---------4-6368
BB------9-88788--------6-7887----34-7637-------45-454545---5-47637---6-7647--c-wdgh
CC------1-23456--------4-5678---7-8912

I am trying to get result as below
ColA-----------ColB
AA-----------3-rt567
AA-----------4-6368
BB-----------9-88788
BB-----------6-7887
BB-----------34-7637
BB-----------45-454545
BB-----------5-47637
BB-----------6-7647
BB-----------c-wdgh
CC-----------1-23456
CC-----------4-5678
CC-----------7-8912

Thanks in advance for your valuable advices and solutions

Thanks,
gd

RWomanizer · Answer

supoose that your data is in sheet 1 
now use the macro 
and your required in now in sheet2


Sub newRow()
Dim lstRow, lstrow1 As Long
Dim i, j, k As Integer
Dim Str1, Str2 As String

lstRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To lstRow
    Str1 = Cells(i, 1).Value
    For j = 2 To Columns.Count
    Str2 = Cells(i, j).Value
    If Str2 <> "" Then
    lstrow1 = Worksheets(2).Range("A" & Rows.Count).End(xlUp).Row
    Worksheets(2).Cells(lstrow1, 1).Offset(1, 0).Value = Str1
    Worksheets(2).Cells(lstrow1, 2).Offset(1, 0).Value = Str2
    Else: GoTo 1
    End If
    Next j
1:  Next i

End Sub

Converting rows data into columns on conditon

1 response

Similar discussions

Subscribe To Our Newsletter!