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
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,