How to stack or transform the data in excel

Question

I would like to transform the data from table 1 to table 2 in Excel.

What Excel formula can I use?

Answer 1

You need a Pivot Table. To build the one you want is easy:

• Select your data, including headers
• Go to Insert :-> Pivot Table on the ribbon
• Click OK on the 'Create PivotTable' dialog that appears
• In the next dialog 'PivotTable Fields', place id in Rows, subid in Columns, and measure in Values
• That's it!