Changing data orientation from one sheet to another

  • Thread starter Thread starter YMTEO
  • Start date Start date
Y

YMTEO

Hi,

I have a report submitted which looks like a pivot table format and would
like to change it to another orientation in another worksheet.
Is there a faster way to do that?

Original format:-
Customer Country1 Name Data Aug-07 Sep-07 Oct-07
Cust A Malaysia Apple Sales 7,253 2,115
Cust A Malaysia Apple Volume 480 140
Cust A Malaysia Apple SASP 15.110 15.110
Cust A Malaysia Orange Sales 3,050 10,750 11,150
Cust A Malaysia Orange Volume 15,250 53,750 55,750
Cust A Malaysia Orange SASP 0.200 0.200 0.200
Cust B US Pear Sales 2,369
Cust B US Pear Volume 360
Cust B US Pear SASP 6.580
Cust B US Orange Sales 6,075 9,113 13,669
Cust B US Orange Volume 900 1,350 2,025
Cust B US Orange SASP 6.750 6.750 6.750
Cust C China Pear Sales 570 570 760
Cust C China Pear Volume 30 30 35
Cust C China Pear SASP 19.000 19.000 21.714
Cust D US Cherry Sales 5,832 4,860
Cust D US Cherry Volume 129,600 108,000
Cust D US Cherry SASP 0.045 0.045
Cust D US Orange Sales 720 24,000 18,000
Cust D US Orange Volume 4 120 90
Cust D US Orange SASP 200.000 200.000 200.000


change it to another format in another worksheet:-

Customer Country1 Name Data Sales Volume SASP
Cust A Malaysia Apple Aug-07 7252.8 480 15.11
Cust A Malaysia Apple Oct-07 2115.4 140 15.11
Cust A Malaysia Orange Aug-07 3,050 15,250 0.200
Cust A Malaysia Orange Sep-07 10,750 53,750 0.200
Cust A Malaysia Orange Oct-07 11,150 55,750 0.200
etc
etc

It would even be better if the 2nd worksheet link to the first data source.
 
Instead of that why not just employ adding/hiding/moving columns back and
forth. A macro can do it for you. Just record it and assign to a shape or
button from the forms toolbar.
 
Hi Don,

Could you guide me how to do that?
How to assign the macro? I am not good at that.
There are lots of rows and would it tak up lots of time in doing it?


Thanks
Yimin
 
If desired, send your workbook to my address below along with snippets of
these messages along with a clear explanation of what you want and
before/after examples. Then, I can take a look.
 
maybe this executed from the source sheet
Sub leusht2()
With Sheets("destinationsheetnamehere")
dlr = .Cells(Rows.Count, 1).End(xlUp).Row
.Range("a2:g" & dlr).ClearContents
For i = 2 To cells(rows.count,1).end(xlup).row Step 3
Set mc = Cells(i, "d")
Set dmc = .Cells(i, "a")
If mc.Offset(, 1) <> "" Then
dmc.Offset(, 0) = mc.Offset(, -3)
dmc.Offset(, 1) = mc.Offset(1, -2)
dmc.Offset(, 2) = mc.Offset(2, -1)
dmc.Offset(, 3) = Range("e1")
dmc.Offset(, 4) = mc.Offset(, 1)
dmc.Offset(, 5) = mc.Offset(1, 1)
dmc.Offset(, 6) = mc.Offset(2, 1)
End If
If mc.Offset(, 2) <> "" Then
dmc.Offset(1, 0) = mc.Offset(, -3)
dmc.Offset(1, 1) = mc.Offset(1, -2)
dmc.Offset(1, 2) = mc.Offset(2, -1)
dmc.Offset(1, 3) = Range("f1")
dmc.Offset(1, 4) = mc.Offset(, 2)
dmc.Offset(1, 5) = mc.Offset(1, 2)
dmc.Offset(1, 6) = mc.Offset(2, 2)
End If
If mc.Offset(, 3) <> "" Then
dmc.Offset(2, 0) = mc.Offset(, -3)
dmc.Offset(2, 1) = mc.Offset(1, -2)
dmc.Offset(2, 2) = mc.Offset(2, -1)
dmc.Offset(2, 3) = Range("g1")
dmc.Offset(2, 4) = mc.Offset(, 3)
dmc.Offset(2, 5) = mc.Offset(1, 3)
dmc.Offset(3, 6) = mc.Offset(2, 3)
End If
Next i
.Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
End Sub
 
Back
Top