A complicated reformatting problem in excel

bobomaggie

New Member
Joined
Jan 29, 2014
Messages
3
Anyone has a neat solution to this reformatting problem? I have a huge data set that needs to be reformatted from A to B. The trick is that students do not necessarily take the same number/type of tests. Thanks!

Format A

Student ID Subject Score
a reading 343
a math 434
a history 453
a science 454
b algebra I 454
b reading 342
c geometry 334
c history 343
c reading 450

Format B

Student ID Reading Math History Science Algebra I Geometry
a 343 434 453 454
b 342 454
c 450 343
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

Have you tried a Pivot Table?

<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 /><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><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Sum of Score</td><td style=";">Column Labels</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><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Row Labels</td><td style="text-align: right;;">algebra I</td><td style="text-align: right;;">geometry</td><td style="text-align: right;;">history</td><td style="text-align: right;;">math</td><td style="text-align: right;;">reading</td><td style="text-align: right;;">science</td><td style="text-align: right;;">Grand Total</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">a</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">453</td><td style="text-align: right;;">434</td><td style="text-align: right;;">343</td><td style="text-align: right;;">454</td><td style="text-align: right;;">1684</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">b</td><td style="text-align: right;;">454</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">342</td><td style="text-align: right;;"></td><td style="text-align: right;;">796</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">c</td><td style="text-align: right;;"></td><td style="text-align: right;;">334</td><td style="text-align: right;;">343</td><td style="text-align: right;;"></td><td style="text-align: right;;">450</td><td style="text-align: right;;"></td><td style="text-align: right;;">1127</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Grand Total</td><td style="text-align: right;;">454</td><td style="text-align: right;;">334</td><td style="text-align: right;;">796</td><td style="text-align: right;;">434</td><td style="text-align: right;;">1135</td><td style="text-align: right;;">454</td><td style="text-align: right;;">3607</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 />


HTH,
 

bobomaggie

New Member
Joined
Jan 29, 2014
Messages
3
AHA!!! and in the value field you just keep sum of score. In that case I just have to make sure there is no duplicate scores for the same student. Oh my, you made this so easy. Thank you!!


Welcome to the Board!

Have you tried a Pivot Table?

Excel 2010
ABCDEFGH
3Sum of ScoreColumn Labels
4Row Labelsalgebra IgeometryhistorymathreadingscienceGrand Total
5a4534343434541684
6b454342796
7c3343434501127
8Grand Total45433479643411354543607

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet4




HTH,
 

Watch MrExcel Video

Forum statistics

Threads
1,122,670
Messages
5,597,483
Members
414,146
Latest member
marginmakerb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top