| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Col 1 & 2 to display info in rows
Am using Office 2K. I have used Pivot Tables and am still learning more about
it. I have generated a Pivot Table (with about 5000 lines) that shows the following info: i.e Acc No 201 was JKB Ltd. The company changed its anme to JKB Sys. We have kept the same Acc No. It has taken the parts 4737, 4790, 5050, 4532 & 4790. Acc No Acc Name Part No 3 4 5 Grand Total 201 JKB Ltd 4737 311 311 4790 371 371 5050 293 293 JKB Sys 4532 21 155 90 266 4790 153 330 320 803 355 The C Grp 2000 390 220 255 865 2001 900 440 555 1895 4000 310 160 150 620 PTL Group 2000 224 224 2001 395 395 4000 105 105 Is it possible to get the info in Acc No and Acc Name to be displayed as follows: where the Acc No and Acc Name are shown on the screen Acc No Acc Name Part No 3 4 5 Grand Total 201 JKB Ltd 4737 311 311 201 JKB Ltd 4790 371 371 201 JKB Ltd 5050 293 293 201 JKB Sys 4532 21 155 90 266 201 JKB Sys 4950 153 330 320 803 355 The C Grp 2000 390 220 255 865 355 The C Grp 2001 900 440 555 1895 355 The C Grp 4000 310 160 150 620 355 PTL Group 2000 224 224 355 PTL Group 2001 395 395 355 PTL Group 4000 105 105 Thanks a lot |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot Table Col 1 & 2 to display info in rows
Hi Johnny
There are a couple of methods described on Debra Dalgleish's site that will help you to do this. http://www.contextures.com/xlDataEntry02.html Alternatively you code add a column to your source data with the formula =IF(B2="JKB Sys", 201.1,A2) and fill down. Use this new column in the PT instead of the present one, then at least you would see 201 JKB Ltd 4737 ...............................4790 ...............................5050 201.1 JKB Sys 4532 ...............................4790 (dots inserted just for alignment in posting) -- Regards Roger Govier "Johnny" wrote in message ... Am using Office 2K. I have used Pivot Tables and am still learning more about it. I have generated a Pivot Table (with about 5000 lines) that shows the following info: i.e Acc No 201 was JKB Ltd. The company changed its anme to JKB Sys. We have kept the same Acc No. It has taken the parts 4737, 4790, 5050, 4532 & 4790. Acc No Acc Name Part No 3 4 5 Grand Total 201 JKB Ltd 4737 311 311 4790 371 371 5050 293 293 JKB Sys 4532 21 155 90 266 4790 153 330 320 803 355 The C Grp 2000 390 220 255 865 2001 900 440 555 1895 4000 310 160 150 620 PTL Group 2000 224 224 2001 395 395 4000 105 105 Is it possible to get the info in Acc No and Acc Name to be displayed as follows: where the Acc No and Acc Name are shown on the screen Acc No Acc Name Part No 3 4 5 Grand Total 201 JKB Ltd 4737 311 311 201 JKB Ltd 4790 371 371 201 JKB Ltd 5050 293 293 201 JKB Sys 4532 21 155 90 266 201 JKB Sys 4950 153 330 320 803 355 The C Grp 2000 390 220 255 865 355 The C Grp 2001 900 440 555 1895 355 The C Grp 4000 310 160 150 620 355 PTL Group 2000 224 224 355 PTL Group 2001 395 395 355 PTL Group 4000 105 105 Thanks a lot |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot Table Col 1 & 2 to display info in rows
Hi Roger
Thanks for the help. I misunderstood it the first time. I went to Debra D's site and the solution was written very clearly and simple to understand method was use. Thanks a lot to you and Debra and also for the quick response. Keep up the good work. "Roger Govier" wrote: Hi Johnny There are a couple of methods described on Debra Dalgleish's site that will help you to do this. http://www.contextures.com/xlDataEntry02.html Alternatively you code add a column to your source data with the formula =IF(B2="JKB Sys", 201.1,A2) and fill down. Use this new column in the PT instead of the present one, then at least you would see 201 JKB Ltd 4737 ...............................4790 ...............................5050 201.1 JKB Sys 4532 ...............................4790 (dots inserted just for alignment in posting) -- Regards Roger Govier "Johnny" wrote in message ... Am using Office 2K. I have used Pivot Tables and am still learning more about it. I have generated a Pivot Table (with about 5000 lines) that shows the following info: i.e Acc No 201 was JKB Ltd. The company changed its anme to JKB Sys. We have kept the same Acc No. It has taken the parts 4737, 4790, 5050, 4532 & 4790. Acc No Acc Name Part No 3 4 5 Grand Total 201 JKB Ltd 4737 311 311 4790 371 371 5050 293 293 JKB Sys 4532 21 155 90 266 4790 153 330 320 803 355 The C Grp 2000 390 220 255 865 2001 900 440 555 1895 4000 310 160 150 620 PTL Group 2000 224 224 2001 395 395 4000 105 105 Is it possible to get the info in Acc No and Acc Name to be displayed as follows: where the Acc No and Acc Name are shown on the screen Acc No Acc Name Part No 3 4 5 Grand Total 201 JKB Ltd 4737 311 311 201 JKB Ltd 4790 371 371 201 JKB Ltd 5050 293 293 201 JKB Sys 4532 21 155 90 266 201 JKB Sys 4950 153 330 320 803 355 The C Grp 2000 390 220 255 865 355 The C Grp 2001 900 440 555 1895 355 The C Grp 4000 310 160 150 620 355 PTL Group 2000 224 224 355 PTL Group 2001 395 395 355 PTL Group 4000 105 105 Thanks a lot |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| OLAP Pivot table - How to show items with no data ? | Excel Worksheet Functions | |||
| Select all subtotal rows on Pivot Table | Excel Worksheet Functions | |||
| Why does (blank) display on pivot table | Charts and Charting in Excel | |||
| adding rows to pivot table | Excel Discussion (Misc queries) | |||
| Need Formula to display pivot table source data | Excel Worksheet Functions | |||
Linear Mode
