breaking apart columns in pivot table

[vbasean]

I have a pivot that needs multiple columns instead of overlapping columns

here's an example

PivotField1
Item1, Item2, Item3

PivotField2
Item1, Item2, Item3

PivotField3...

when I place these columns along side each other this is what I get:
PivotField1, PivotField2, ...
Item1 Item2
Item1, Item, Item2 Item1, Item2, Item3

It keeps tiering off of each column and I don't want that.

I want Each column with it's own subcolumns SEPARATE.

Any suggestions?

Thanks

 

[Sean Timmons]

When you drop your items, you can either drop them above or below. If I'm
reading right, you'd want to grap pivot field and drop, then drop the items
below the categories displayed.
Pivotfield1
list
drag and drop here

That what you were looking to do?

 

[vbasean]

no, when I place columns beside each other they overlap their sub columns.

take this for example

say I have States that send me fruit
Washington
Apples, Oranges, Grapes

Michigan
Apples, Oranges, Grapes

When I place these columns along side each other I get this tiered mess
Washington Michigan
Apples Apples
Apples, Oranges, Grapes Apples, Oranges, Grapes

and more convoluted


What I want is basic.

Washington Michigan
Apples, Oranges, Grapes Apples, Oranges, Grapes

 

[Jim Thomlinson]

I really don't see what you are trying to do here. Can you give is a really
small extract of how your source data is configured and a cut and paste of
how your pivot table currently looks. Then show us how you want the pivot
table to look in the end...

 

[Sean Timmons]

I'm getting it the way you're looking for it on my pivot.

I have a table of:

State Fruit
Washington Apples
Washington Oranges
Washington Grapes
Michigan Apples
Michigan Oranges
Michigan Grapes

and I get

Michigan Washington
Apples Grapes Oranges Apples Grapes Oranges

Maybe there's something more complex in the actual table that I'm not aware
of?

 

[Jim Thomlinson]

What does your source data look like... What is the structure? Can you give
us the column headers and a couple of lines of data. To me it sounds like
your source data is not structured correctly to efficiently pivot.

 

[vbasean]

well, there's more to it (isn't there always... lol)
using below sample, the state IS a column in the raw data

Washington Michigan
---------------------------------
Apples Apples
Oranges Grapes
Grapes Pinapples
Apples Advocatos
Oranges Apples
Grapes Oranges

 

[Sean Timmons]

There's your issue. Best to have, say,

State Fruit
Washington Apple
Washington Orange

etc.

Then, it'd work better. In the below example, you could just make your own
table, make your headers, then do countif statements in the value cells
below...

 

[vbasean]

Thanks Sean,

You and Jim helped me see the light. My query is already complex but I'm
hard coding the "State" name into the query (actually it's not 'State' but
information ...) so I have a State column instead of multiple states in
columns. Then I'll union these queries together so there's one "State"
column. Then my pivot will have the two tiered columns
-State
-Fruit

thanks all

 

[Sean Timmons]

That ought to get it! Glad to help!

Thanks Sean,

You and Jim helped me see the light. My query is already complex but I'm
hard coding the "State" name into the query (actually it's not 'State' but
information ...) so I have a State column instead of multiple states in
columns. Then I'll union these queries together so there's one "State"
column. Then my pivot will have the two tiered columns
-State
-Fruit

thanks all