Converting rows data into columns on conditon [Solved/Closed]

Report
-
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
-
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

1 reply

Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
116
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
Thank You .It works fine..
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
116
You are welcomed

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!