Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Customized arrangement of pivot table
Hi, I use pivot table for grouping items to get a source file for CSV export
file. My CSV file has to have same structure for each line (record line). I want my pivot table to aggregate same kind of data (which it does) and show row labels in each line (which is does not). Example of pivot table row labels part looks like: 01 2009 NL CR 11 3 K 08043000 1 ES 11 3 K 07051100 1 IL 11 3 K 12119085 1 07039000 1 09109933 1 SK CN 11 3 K 07032000 1 TR 11 3 K 08054000 1 I want it showing 01, 2009, 11, 3 and K in each row; in column 3 NL in rows 1-5, SK in rows 6-7; in column 4 IL in rows 3-5. So far, I have to create separate column away from pilot with formulas letting all cells in 7x7 box to be filled in. I would like to avoid additional columns. Is there a way to have all cells filled in directly in pivot? Excel 2007, but should be similar in 2003. Thanks for help in advance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Customized arrangement of pivot table
Hi,
Since you are going to create a CSV file from it, a pivot table is not critical. 1. Select the pivot table and choose Copy, 2. move to a blank sheet and choose Edit, Paste Special, Values. 3. Select all the data and press F5, Special, Blanks, OK. 4. From the keyboard (no mouse) type =, Up Arrow, Ctrl+Enter (that is equal followed by pressing up arrow, and then Ctrl Enter). This should fill in all the missing data. 5. Optional - select the whole range, copy it, paste special values (to get rid of the formulas. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Alojz" wrote: Hi, I use pivot table for grouping items to get a source file for CSV export file. My CSV file has to have same structure for each line (record line). I want my pivot table to aggregate same kind of data (which it does) and show row labels in each line (which is does not). Example of pivot table row labels part looks like: 01 2009 NL CR 11 3 K 08043000 1 ES 11 3 K 07051100 1 IL 11 3 K 12119085 1 07039000 1 09109933 1 SK CN 11 3 K 07032000 1 TR 11 3 K 08054000 1 I want it showing 01, 2009, 11, 3 and K in each row; in column 3 NL in rows 1-5, SK in rows 6-7; in column 4 IL in rows 3-5. So far, I have to create separate column away from pilot with formulas letting all cells in 7x7 box to be filled in. I would like to avoid additional columns. Is there a way to have all cells filled in directly in pivot? Excel 2007, but should be similar in 2003. Thanks for help in advance. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Customized arrangement of pivot table
"Shane Devenshire" wrote: Hi, Since you are going to create a CSV file from it, a pivot table is not critical. 1. Select the pivot table and choose Copy, 2. move to a blank sheet and choose Edit, Paste Special, Values. 3. Select all the data and press F5, Special, Blanks, OK. 4. From the keyboard (no mouse) type =, Up Arrow, Ctrl+Enter (that is equal followed by pressing up arrow, and then Ctrl Enter). This should fill in all the missing data. 5. Optional - select the whole range, copy it, paste special values (to get rid of the formulas. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Alojz" wrote: Hi, I use pivot table for grouping items to get a source file for CSV export file. My CSV file has to have same structure for each line (record line). I want my pivot table to aggregate same kind of data (which it does) and show row labels in each line (which is does not). Example of pivot table row labels part looks like: 01 2009 NL CR 11 3 K 08043000 1 ES 11 3 K 07051100 1 IL 11 3 K 12119085 1 07039000 1 09109933 1 SK CN 11 3 K 07032000 1 TR 11 3 K 08054000 1 I want it showing 01, 2009, 11, 3 and K in each row; in column 3 NL in rows 1-5, SK in rows 6-7; in column 4 IL in rows 3-5. So far, I have to create separate column away from pilot with formulas letting all cells in 7x7 box to be filled in. I would like to avoid additional columns. Is there a way to have all cells filled in directly in pivot? Excel 2007, but should be similar in 2003. Thanks for help in advance. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Customized arrangement of pivot table
Shane,
to copy special, values is what I knew, I use it very often to get rid of formulas (I wrote short code for copying values, now I do it always through ctrl+shift+v :-)). Very valuable advice u provided me with was F5 - a very smart hint. Even though I knew F5 feature too, I did not think about it in this case. Thank you for nice and smart solution!!! "Shane Devenshire" wrote: Hi, Since you are going to create a CSV file from it, a pivot table is not critical. 1. Select the pivot table and choose Copy, 2. move to a blank sheet and choose Edit, Paste Special, Values. 3. Select all the data and press F5, Special, Blanks, OK. 4. From the keyboard (no mouse) type =, Up Arrow, Ctrl+Enter (that is equal followed by pressing up arrow, and then Ctrl Enter). This should fill in all the missing data. 5. Optional - select the whole range, copy it, paste special values (to get rid of the formulas. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Alojz" wrote: Hi, I use pivot table for grouping items to get a source file for CSV export file. My CSV file has to have same structure for each line (record line). I want my pivot table to aggregate same kind of data (which it does) and show row labels in each line (which is does not). Example of pivot table row labels part looks like: 01 2009 NL CR 11 3 K 08043000 1 ES 11 3 K 07051100 1 IL 11 3 K 12119085 1 07039000 1 09109933 1 SK CN 11 3 K 07032000 1 TR 11 3 K 08054000 1 I want it showing 01, 2009, 11, 3 and K in each row; in column 3 NL in rows 1-5, SK in rows 6-7; in column 4 IL in rows 3-5. So far, I have to create separate column away from pilot with formulas letting all cells in 7x7 box to be filled in. I would like to avoid additional columns. Is there a way to have all cells filled in directly in pivot? Excel 2007, but should be similar in 2003. Thanks for help in advance. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Customized arrangement of pivot table
Your welcome and thanks for the feedback.
Cheers, Shane Devenshire "Alojz" wrote: Shane, to copy special, values is what I knew, I use it very often to get rid of formulas (I wrote short code for copying values, now I do it always through ctrl+shift+v :-)). Very valuable advice u provided me with was F5 - a very smart hint. Even though I knew F5 feature too, I did not think about it in this case. Thank you for nice and smart solution!!! "Shane Devenshire" wrote: Hi, Since you are going to create a CSV file from it, a pivot table is not critical. 1. Select the pivot table and choose Copy, 2. move to a blank sheet and choose Edit, Paste Special, Values. 3. Select all the data and press F5, Special, Blanks, OK. 4. From the keyboard (no mouse) type =, Up Arrow, Ctrl+Enter (that is equal followed by pressing up arrow, and then Ctrl Enter). This should fill in all the missing data. 5. Optional - select the whole range, copy it, paste special values (to get rid of the formulas. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Alojz" wrote: Hi, I use pivot table for grouping items to get a source file for CSV export file. My CSV file has to have same structure for each line (record line). I want my pivot table to aggregate same kind of data (which it does) and show row labels in each line (which is does not). Example of pivot table row labels part looks like: 01 2009 NL CR 11 3 K 08043000 1 ES 11 3 K 07051100 1 IL 11 3 K 12119085 1 07039000 1 09109933 1 SK CN 11 3 K 07032000 1 TR 11 3 K 08054000 1 I want it showing 01, 2009, 11, 3 and K in each row; in column 3 NL in rows 1-5, SK in rows 6-7; in column 4 IL in rows 3-5. So far, I have to create separate column away from pilot with formulas letting all cells in 7x7 box to be filled in. I would like to avoid additional columns. Is there a way to have all cells filled in directly in pivot? Excel 2007, but should be similar in 2003. Thanks for help in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
Pivot Table Customized Formulas | Excel Discussion (Misc queries) | |||
Arrangement | Excel Discussion (Misc queries) | |||
Re-arrangement | Excel Discussion (Misc queries) | |||
Pivot Table Customized Views | Excel Discussion (Misc queries) |