How to Properly Align Fields in a Pivot Table

[wec43wec]

I need to know how to adjustment my pivot table to show each of two items I've selected and placed in the "row" layout of my pivot.

As an example, using the following criteria, the name of each "state" is only displayed on the first line of each group of cities. I need the state to be listed on the "same line" as each listing of each city.

Sample data:


State City Population Male Female
Ny City-1 1000 450 550
City-2 2000 1400 600
City-3 3000 1500 1500
City-4 2000 1700 300
NC City-1 1000 450 550
City-2 4000 2000 2000
City-3 6000 3000 3000







-

 

[SkipVought]

Hi,

That's just the way the the PivotTable Wizard performs.

You could use MSQuery, but you would need to know how to code the SQL for a transform query. faq68-5829.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[wec43wec]

Skip could I use Access to sort the data and trans
fer the data back to excel?

 

[SkipVought]

You could use a formula to propogte the values thru all cells like...
[tt]
A2: =If(b2=b1,a1,b2)
[tt]
Then hide the column.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Another way might be to use the Advanced filter to generate a unique list of State & City values. But this would involve a copy & paste operation to align the rows with your PT.

I'd still opt for MS Query, myself.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}