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?

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
Sheet4

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
Sum of Score	Column Labels
Row Labels
a
b
c
Grand Total

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]algebra I[/TD]
[TD="align: right"]geometry[/TD]
[TD="align: right"]history[/TD]
[TD="align: right"]math[/TD]
[TD="align: right"]reading[/TD]
[TD="align: right"]science[/TD]
[TD="align: right"]Grand Total[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]453[/TD]
[TD="align: right"]434[/TD]
[TD="align: right"]343[/TD]
[TD="align: right"]454[/TD]
[TD="align: right"]1684[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]454[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]342[/TD]
[TD="align: right"][/TD]
[TD="align: right"]796[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"]334[/TD]
[TD="align: right"]343[/TD]
[TD="align: right"][/TD]
[TD="align: right"]450[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1127[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]454[/TD]
[TD="align: right"]334[/TD]
[TD="align: right"]796[/TD]
[TD="align: right"]434[/TD]
[TD="align: right"]1135[/TD]
[TD="align: right"]454[/TD]
[TD="align: right"]3607[/TD]

</tbody>

Sheet4​

HTH,