Convert matrix to 3-column table ('reverse pivot', 'unpivot', 'flatten', 'normalize')

Question

I need to convert the Excel matrix FIRST in the table LATER:

FIRST:

    P1  P2  P3  P4 F1  X F2  X   X F3      X       X F4      X   X 

LATER:

F   P   VALUE F1  P1  X F1  P2 F1  P3 F1  P4 F2  P1  X F2  P2  X F2  P3 F2  P4 F3  P1 F3  P2  X F3  P3 F3  P4  X F4  P1 F4  P2  X F4  P3  X F4  P4 

Answer 1

To “reverse pivot”, “unpivot” or “flatten”:

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

   

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

For Excel for Mac 2011, it's ⌘+Alt+P (See here).

2. Select Multiple consolidation ranges and click Next.

   

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

   

4. In “Step 2b of 3” specify your summary table range in the Range field (A1:E5 for the sample data) and click Add, then Next.

   

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

   

6. Click Finish to create the pivot table:

   

7. Drill down (ie double-click) on the intersect of the Grand Totals (here Cell V7 or 7):

   

8. The PT may now be deleted.

9. The resulting Table may be converted to a conventional array of cells by selecting Table in the Quick Menu (right-click in the Table) and Convert to Range.

---

There is a video on the same subject at Launch Excel which I consider excellent quality.