Moving data into particular format

[Pauls888]

We have some results data that has been given to us in an excel spreadsheet
but is not set out as we would like. Not sure how to go about getting how we
want it, but I'm sure it must be possible.

Data currently has results for each student. The students unique number is
listed in column A and the subject studied in B with grades in C, D and E.
Each student is listed for each subject they study in rows with grades in the
columns for each subject. What we would like is to have the student
appearing in the row once and have a column for each subject's grades (ie 3
columns per subject).

Data as it is now
Column A Column B Column C Column D Column E
Student 1 Business A B B
Student 1 English A A B

What we would like
Column A Bus Grd 1 Bus Grd 2 Bus Grade 3 Eng Grade 1 Eng Grd 2 Eng
Grd 3
Student1 A B B A
A B

Any suggestions as to where we start?

 

[Heidi]

Pauls888,

You might look at PivotTables. Select your data, including column headings
(make sure you have headings for each column). Make sure there are no blank
rows. Go to Data>PivotTable and PivotChart Report and just hit finish.

Drag "student id" from the little pop-up window to the rows column,
"subject" to the header, and grades 1, 2, and 3 to the data.

Heidi

 

[Pauls888]

Thanks Heidi, but unfortunately that's the first thing we tried - it gives us
the correct layout, but the grades do not appear, only the tally, unless
there is a way to show the data in the box rather than the "count"

 

[Heidi]

If you right-click in the column, go to "Field Settings" and choose one of
the other options.

You're right, it will probably average the grades for you, and not show them
individually, though. But, you can at least move beyond "count".

Heidi

 

[Heidi]

Paul,

I just did this with some dummy data and it does exactly what you want.
Email me at: hbissell at memphiszoo dot org and I'll send you the file.