Customized arrangement of pivot table

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.
 
S

Shane Devenshire

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.
 
A

Alojz

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...
 
A

Alojz

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!!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Question on the occupied field of the pivot table in excel spreadsheet 0
Sorting Count Values in Pivot Table (Excel 2007) 1
Different perspective of RANK 4
Getting "Per Item" Pivot Chart Results when data rows are identica 4
Pivot Table - 3
Getting Detailed Pivot Results when some row data is identical 4
Pivot Table Set Up 2
Copying values from pivot table to cells outside pivot table 4

Top