Convert data set into flat data structure

[alee_tan]

Hello,

May I know if there is a quick and simple way to convert my dataset into a flat data structure?

I am working with a data set structured as follows:

Year A B C
2009 1 2 3
2010 4 5 6

Is there an easy way to make it into the following structure:

Year Category Value
2009 A 1
2009 B 2
2009 C 3
2010 A 4
2010 B 5
2010 C 6

Thanks in advance!

 

[Richard Schollar]

Hi & Welcome to the Board!

You can do this via a pivot table (believe it or not):

1. Select your data including headers
2. Use Alt+D+P to open up the Pivot Table dialog (you must do this step if using xl2007 or later - you cannot use the ribbon)
3. Under "Where is the data that you want to analyze" select "Multiple Consolidation Ranges">Next
4. Leave "Create a single page field for me">Next
5. Enter your range under the "Where are the worksheet ranges that you want to consolidate?" header (I know you only have one range)>Click the Add button>Finish

This will give you an output that looks like this:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="background-color: #DAEEF3;;">Page1</td><td style="background-color: #DAEEF3;;">(All)</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="background-color: #DAEEF3;;">Sum of Value</td><td style="background-color: #DAEEF3;;">Column</td><td style="text-align: right;background-color: #DAEEF3;;"></td><td style="text-align: right;background-color: #DAEEF3;;"></td><td style="text-align: right;background-color: #DAEEF3;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="background-color: #DAEEF3;;">Row</td><td style="background-color: #DAEEF3;;">A</td><td style="background-color: #DAEEF3;;">B</td><td style="background-color: #DAEEF3;;">C</td><td style="background-color: #DAEEF3;;">Grand Total</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">2009</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">2010</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">15</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="background-color: #DAEEF3;;">Grand Total</td><td style="text-align: right;background-color: #DAEEF3;;">5</td><td style="text-align: right;background-color: #DAEEF3;;">7</td><td style="text-align: right;background-color: #DAEEF3;;">9</td><td style="text-align: right;background-color: #DAEEF3;;">21</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet4</p><br /><br />

Except that if you are using xl2007 or greater you will need to right-click in the PT and select "Pivot Table Options" and click on the Display tab and check "Classic PivotTable layout".

6. Drag (select and hold left mouse button) on the label "Column" and drag it over to be beside the Row field. Right-click on the Row field and choose "Field Settings" and make sure Subtotals is set to None

This will give you a table that looks like:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="background-color: #DAEEF3;;">Page1</td><td style="background-color: #DAEEF3;;">(All)</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="background-color: #DAEEF3;;">Sum of Value</td><td style="text-align: right;background-color: #DAEEF3;;"></td><td style="text-align: right;background-color: #DAEEF3;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="background-color: #DAEEF3;;">Row</td><td style="background-color: #DAEEF3;;">Column</td><td style="background-color: #DAEEF3;;">Total</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">2009</td><td style="background-color: #DAEEF3;;">A</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="background-color: #DAEEF3;;">B</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="background-color: #DAEEF3;;">C</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">2010</td><td style="background-color: #DAEEF3;;">A</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="background-color: #DAEEF3;;">B</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="background-color: #DAEEF3;;">C</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="background-color: #DAEEF3;;">Grand Total</td><td style="text-align: right;background-color: #DAEEF3;;"></td><td style="text-align: right;background-color: #DAEEF3;;">21</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet4</p><br /><br />

7. Select all the cells and copy>pastespecial>values to fix the values (and kill the PT)
8. Select column A and hit F5>click Special button>Select: Blanks
9. Immediately type '=' and press the up cursor key and confirm with Ctrl+Enter (not just enter - this will enter the formula you have created in all blank cells).

This will give you:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="background-color: #DAEEF3;;">Page1</td><td style="background-color: #DAEEF3;;">(All)</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="background-color: #DAEEF3;;">Sum of Value</td><td style="text-align: right;background-color: #DAEEF3;;"></td><td style="text-align: right;background-color: #DAEEF3;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="background-color: #DAEEF3;;">Row</td><td style="background-color: #DAEEF3;;">Column</td><td style="background-color: #DAEEF3;;">Total</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">2009</td><td style="background-color: #DAEEF3;;">A</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">2009</td><td style="background-color: #DAEEF3;;">B</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">2009</td><td style="background-color: #DAEEF3;;">C</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">2010</td><td style="background-color: #DAEEF3;;">A</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">2010</td><td style="background-color: #DAEEF3;;">B</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">2010</td><td style="background-color: #DAEEF3;;">C</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="background-color: #DAEEF3;;">Grand Total</td><td style="text-align: right;background-color: #DAEEF3;;"></td><td style="text-align: right;background-color: #DAEEF3;;">21</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">A6</th><td style="text-align:left">=A5</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">A7</th><td style="text-align:left">=A6</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">A9</th><td style="text-align:left">=A8</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">A10</th><td style="text-align:left">=A9</td></tr></tbody></table></td></tr></table><br />

10. Copy and pastespecial values again to fix the formulas and that's it! You now have normalised data. Clean up the headers and hey presto!

 

[alee_tan]

Hi Richard,

Works like a charm. Thank you so much!

 

[Andrew Poulsom]

If you check 'I will create the page fields' at Step 2a, you can double-click the cell at the bottom right of the pivot table to create a new sheet with your data in first normal form. You will need to change the headings fro Row and Column to what you want.

 

[Richard Schollar]

If you check 'I will create the page fields' at Step 2a, you can double-click the cell at the bottom right of the pivot table to create a new sheet with your data in first normal form. You will need to change the headings fro Row and Column to what you want.

Wow! That was slick - makes it VERY easy!