A complicated reformatting problem in excel

[bobomaggie]

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

 

[Smitty]

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]

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

	A	B	C	D	E	F	G	H
3	Sum of Score	Column Labels
4	Row Labels	algebra I	geometry	history	math	reading	science	Grand Total
5	a			453	434	343	454	1684
6	b	454				342		796
7	c		334	343		450		1127
8	Grand Total	454	334	796	434	1135	454	3607

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

Sheet4​

HTH,