Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

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

Answer 1 / 1
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
Report

Thank You .It works fine..
Report
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
 116
You are welcomed
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!