Search code examples
vbaexcelexcel-formulaunpivot

Macro or method to pivot data in Excel?

I am looking for a way to dynamically pivot data in an Excel sheet to put the data in a universal format no matter how wide the data set is.

For example: I want to be able to take a spreadsheet that could have 5 columns, 30 columns, or x number of columns to make it pivot to a universal format that is only three columns wide.

Here is a sample of how the original data could look:

enter image description here

I want it to look like this:

enter image description here

Is there a way to do it, either by macro or using any other Excel functions?

Solution

  • Assumes labels/data is in Sheet1 starting A1.

    1. Insert a new ColumnA.

    2. For Excel 2003: Activate any cell in your summary table and choose Data - PivotTable and PivotChart Report:

      SO20541905 first example

    3. For later versions access the Wizard with Alt+D, P:

    4. Select Multiple consolidation ranges and PivotTable and click Next >.

      SO20541905 second example

    5. In “Step 2a of 3”, choose I will create the page fields and click Next >.

      SO20541905 third example

    6. In “Step 2b of 3” (not shown in the modal!) specify your summary table range in the Range: field (A2:E6 for the sample data) and click Add, then Next >.

    SO32982294 fourth example

    1. In “Step 3 of 3”, select a location for the PivotTable (the existing sheet should serve, as the PT is only required temporarily):

    SO32982294 fifth example

    1. Click Finish to create the PivotTable:

    SO32982294 sixth example

    1. Drill down (ie double-click) on the intersect of the Grand Totals (here Cell L4 or 16):

    SO32982294 seventh example

    1. The PT may now be deleted.

    2. In A2 of Table:

      ="Summary"&RIGHT(B2)

    3. Convert the resulting Table into a conventional array of cells by selecting Table in the Quick Menu (right-click in the Table) and Convert to Range.

    4. In a spare column starting in Row2 series fill integers down, from 1 to however many rows of data you started with (4, in the example). Then series fill that series to suit.

    5. Sort sheet by that (spare) column, Smallest to Largest, with My data has headers checked.

    6. Delete Row1 and spare column and hopefully you will end up with:

      SO32982294 eighth example