# Converting rows data into columns on conditon

Solved/Closed
gd - Mar 29, 2011 at 03:50 AM
RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 - Mar 29, 2011 at 07:53 AM
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,
gd

## 1 response

RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 120
Mar 29, 2011 at 06:09 AM
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..
RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 120
Mar 29, 2011 at 07:53 AM
You are welcomed