Pivot table formatting question 4

[MSIsam]

I use the pivot table function often and waa wondering if there is a way to have the format of the table like that of a normal database table, without haveing spaces for records with duplicate values. For example, this a a typical result for a pivot table:

PivotCol1  PivotCol2 PivotCol3
USA             CA          10
                NY          20
                TX          30
MEXICO          JAL         40
                BCS         50
                            60

I would like to see:

PivotCol1  PivotCol2 PivotCol3
USA             CA          10
USA             NY          20
USA             TX          30
MEXICO          JAL         40
MEXICO          BCS         50
MEXICO          BCS         60

Can this be done?

 

[Lilliabeth]

What does the source data look like?

 

[MSIsam]

This was just a representative example.
The situation that this frequently occurs is when I have more than one dimension in the row field (ex: Country and State), a dimension in the column field (Ex: Month) and a metric in the data items field (Ex: Sales).

 

[xlbo]

pnly by destroying the pivot table

If you copy/paste special>Values, there is a little trick that can be done to infill the gaps - not much good if you can't 'value' the pivot table tho....let me know....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

I know that people sometimes put a couple of columns of formulae before a PivotTable to propigate the titles that way, keeping the PivotTable intact, but having the full set of titles to use in look-ups and other things.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[MSIsam]

Thanks everyone,

xlbo - I would be very interested in that "little trick" even if it is after the pivot table is "destroyed".

Thanks,
Sam

 

[xlbo]

no probs - learnt this from KenWright:


Copy & PasteSpecial>Values the pivot table
Select all of the column you wish to fill

Edit>Goto>Special>Blanks

hit the "=" key and then press the UP arrow
use CTRL+ENTER

this will apply the same formula to all selected cells
copy and pastespecial>values et voila

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[MSIsam]

In your directions...
I'm pretty sure that I am getting the famous ID-10-T error (IDIOT) but can't figure out what I am doing wrong. any ideas?

Copy & PasteSpecial>Values the pivot table
Select all of the column you wish to fill
Done

Edit>Goto>Special>Blanks
This shows a list of radio buttons and check boxes so I checked "blanks"

hit the "=" key and then press the UP arrow
use CTRL+ENTER
There is no place to enter the "=" on this screen so I just hit the "=" key and then the up arrow key.
The arrow key moves the radio button up one selection and highlights the "formulas" selection. If I do not use the arrow, then the blank values end up highlighted

this will apply the same formula to all selected cells
copy and pastespecial>values et voila
error: "That command cannot be used on multiple selections"

 

[DaleWatson123321]

You're gonna kick yourself ;-)...
You only need to click "Ok" after selecting "blanks".

Regards, Dale Watson

 

[MSIsam]

I am still missing something:
Here is what I did

Copy and paste values for the pivot table
Highlight column
Edit-GoTo
Clicked the special button
checked the Blanks radio button
clicked OK
All blank rows are highlighted
cannot paste values in non-continuous sections

 

[Gavona]

All blank rows are highlighted" I assume you mean that All blank cells are highlighted. The next step is to use a formula to fill these cells with the value from the cell above - so I don't see why you would get a "cannot paste...." error. To re-state the procedure, and add a further step to the end:

Select a range containing blank cells which you want to fill with the value from the cell above

Edit>Goto>Special>Blanks
This shows a list of radio buttons and check boxes.  Check "blanks" then press OK
You now have selected just the blank cells that were within your original selection

hit the "=" key and then press the UP arrow
then use CTRL+ENTER
This places the formula you entered in every selected cell

Now select the entire region and Copy, Pastespecial to values to convert all those formulae to values.

Hope that helps (really useful tip from Ken originally).


Gavin

 

[xlbo]

I think the mising step here is that after you enter the formula with CTRL+ENTER, you need to deselect the 'blanks' and then reselect all the data before doing copy/pastespecial>values.

apologies for not being more precise in my original post

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[KenWright]

A link to the FAQ in case you wanted it:-

How to fill in the blanks in a list of data.

faq68-4741

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[xlbo]

LOL - didn't know you'd done a FAQ on it Ken - that might've been easier !!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[MSIsam]

Thanks everyone,
I got it. The confusion was definitely on my side and this makes perfect sense.

Hope that I did not go overboard with the stars but you all helped me get to the answer!

Sam

 

[KenWright]

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------