How to make Pivot Table from data with multiple layers

**jsohng** · 03-17-2011, 03:07 PM

Does anyone know of an easy way to make a pivot table from a data table that has layers of data? So I have this system that pumps out multilayer data, but unfortunately for me, this data comes out in one column, so when I try to make a pivot table to analyze the data, it does not work with a pivot table.

**alansidman** · 03-17-2011, 04:53 PM

Are you getting an error message "data source reference is not valid"?

Alan

**jsohng** · 03-17-2011, 05:04 PM

No error message, but the data cannot be displayed in the pivot table as I would like. So I would like to have a pivot table where I can have the amounts laid out by month, and type of fruit or vegetable, but since there is a hierarchy for those items, that data is not laid out in a way that allows for use in a pivot table.

**alansidman** · 03-17-2011, 05:31 PM

I understand now. I have a thought but don't know how much work it will be because I don't the magnitude of your file categories.

I would build a table of the items that you want to pivot, ie. cucumbers, tomatoes, etc. Give each one a unique number in a cell to the left. 1, 2, 3, etc. For the items having sums or totals, ie. Fruits, Vegs, etc. give them a unique number also, but make it something like 5001, 5002, etc.
Once this table has been created, you can use it over again. It is only a one time event with only maintenance of new items.

Now in your main table, insert a column to the far left of your table. Do a vlookup to return the numbers in your second table. Once you have completed this, do a sort of the table on the unique numbers. All your summary items like Fruits, Vegs, Produce, etc. will now be at the bottom of your table.

Create your pivot table based upon only the items you have at the top of the table.

A bit of work, but it may be worth it.

Alan