Help Splitting levels from Pivot Table into a table.

[dbmagnus]

I have an Excel2010 pivot table that has three “levels” (Trans_Type, Country, Customer) in the “Row Labels” column, and one “values” column (“Transactions”). I need to put this data into a Table with 4 columns (Trans_Type, Country, Customer, Transactions).

However, when I copy and paste the data from the pivot table all three “levels” in the pivot table reside in the same table column. For example: row 1 has the 1st Trans_Type subtotal, row 2 has 1st Country subtotal, then row 3 begins the Customer detail. There are 4 Trans_Types, up to 20 countries for each Trans_Type, and up to 30 titles for each country…all buried in the same column.

So unless there’s another way to split them out (perhaps a pivot table setting) I figured I needed to come up with some VB code to identify the “breaks” for each “level”. Being a novice VB user I’m unsure of the best way to proceed.

Any suggestions or directions to point me in would be greatly appreciated. Thanks!

[Bob Phillips]

Can you post your workbook so that we can see what you are saying?

[dbmagnus]

Since posting this I’ve solved one problem, just to create a new one. I’ve changed my source Pivot Table to “classic” mode, which takes everything out of one column and puts them in their own. So now I have the four columns I need.

Now when I paste them into the table, I have lots of spaces I need to fill (see the attached sample data). Here’s what I need to do:

1. In the Country column, I want the Country name inserted into each blank cell between its first occurrence and its “Total” line.
2. I want to delete the “Total” row for each country.

The same goes for the Trans_Type column. I want to insert the Trans_Type name into each blank between its first occurance and it’s “Total” line, then delete its “Total” row. This should leave me with just Trans_Types and Country names with no subtotal lines for either.

Thanks for taking a look.

[Bob Phillips]

Get your pivot table to repeat the items. In a group, right-click, choose Field Settings>Layout & Print>Repeat Item Labels.

[dbmagnus]

That was too easy! I've been working with Pivot Tables for years, but I guess never needed that until now. Thanks.