Complex data format

[Durga]

Hi,

I have data in the below format which i want to convert into an organised format as mentioned under heading Sheet2.
Can it be possible using VB code to the same:
please help.


Microsoft Excel - Book34.xls ___Running: xl2002 XP : OS = Windows XP

(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout

A1 =


A B C D E F G H I
1 ONMOB123 0 0 0 0
2 0 481711 40313276 0
3
4 DASONMOB 0 0 0 224
5 9055 0 0 0
6
7 OLDMCA 0 0 0 0
8 0 0 0 0
9
10 IMIVPRTL 0 0 0 0
11 0 1 60 0
12
13
14 Result on Sheet2
15 ONMOB123 0 0 0 0 0 481711 40313276 0
16 DASONMOB 0 0 0 224 9055 0 0 0
17 OLDMCA 0 0 0 0 0 0 0 0
18 IMIVPRTL 0 0 0 0 0 1 60 0
Sheet3


Thanks in Advance.

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim Sh1 As Worksheet
    Dim Rng As Range
    Dim Sh2 As Worksheet
    Dim r As Long
    Set Sh1 = Worksheets("Sheet1")
    Set Sh2 = Worksheets("Sheet2")
    Sh1.Cells.Copy Sh2.Range("A1")
    Set Rng = Sh2.Range("A1:A" & Sh2.Cells(Sh2.Rows.Count, 1).End(xlUp).Row)
    For r = Rng.Rows.Count To 2 Step -3
        With Rng
            .Range(.Cells(r, 1), .Cells(r, 1).End(xlToRight)).Copy .Cells(r - 1, 6)
            .Cells(r, 1).Resize(2).EntireRow.Delete
        End With
    Next r
End Sub

 

[Durga]

Hi,

Thanks for the code but I am not getting the desired result.
Let me put it once more:

The data is in below format on Sheet1:

C1 C2 C3 C4 C5
R1 ONMOB123 0 0 0 0
R2 0 481711 4041312 0
R3
R4 OLDMCA 0 0 0 224
R5 9055 0 0 1000
R6

Row R3 and R6 are blank. Data on Row R2 should be copied on Row R1.

Desired result on Sheet2 should be like below:

C1 C2 C3 C4 C5 C6 C7 C8 C9
R1 ONMOB123 0 0 0 0 0 481711 4041312 0
R2 OLDMCA 0 0 0 224 9055 0 0 1000

Please help

Thanks in Advance.

 

[Andrew Poulsom]

I just tried my code again and it worked perfectly.