Customized arrangement of pivot table

  • Thread starter Thread starter Alojz
  • Start date Start date
A

Alojz

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.
 
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.
 
Shane Devenshire said:
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
Click to expand...
 
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!!!
 
Back
Top