Complex data format

Durga

Board Regular
Joined
Apr 22, 2007
Messages
117
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.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Try:

Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top