| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Changing data orientation from one sheet to another
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. |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Changing data orientation from one sheet to another
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 "Don Guillett" wrote: 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. -- Don Guillett Microsoft MVP Excel SalesAid Software "YMTEO" wrote in message ... 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. |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Changing data orientation from one sheet to another
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. -- Don Guillett Microsoft MVP Excel SalesAid Software "YMTEO" wrote in message ... 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 "Don Guillett" wrote: 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. -- Don Guillett Microsoft MVP Excel SalesAid Software "YMTEO" wrote in message ... 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. |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Changing data orientation from one sheet to another
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).EntireR ow.Delete End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... 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. -- Don Guillett Microsoft MVP Excel SalesAid Software "YMTEO" wrote in message ... 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 "Don Guillett" wrote: 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. -- Don Guillett Microsoft MVP Excel SalesAid Software "YMTEO" wrote in message ... 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. |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Changing data orientation from one sheet to another
Hi,
Thanks. It is working fine. It is so much easiler now. THANKS A LOT...... "Don Guillett" wrote: 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).EntireR ow.Delete End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... 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. -- Don Guillett Microsoft MVP Excel SalesAid Software "YMTEO" wrote in message ... 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 "Don Guillett" wrote: 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. -- Don Guillett Microsoft MVP Excel SalesAid Software "YMTEO" wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Changing data orientation from one sheet to another | New Users to Excel | |||
| Changing txt input into a modified orientation. | New Users to Excel | |||
| Changing orientation of pages | Excel Worksheet Functions | |||
| Changing fill orientation | Excel Discussion (Misc queries) | |||
| Changing chart orientation | Charts and Charting in Excel | |||
Hybrid Mode
