Excel - Combining Rows and Flipping Them

cytorak

I have an extract from a student information system in Excel that looks like this.

Student Class Grade Quarter
John Chemistry 70 1
John Chemistry 80 2
John Math 95 1
John Math 100 2
Alice Chemistry 67 1
Alice Chemistry 47 2
Alice Math 88 1
Alice Math 85 2

What I would like is this:

John 70 80 95 100
Alice 67 47 88 85

However, since there are hundreds of students, this would be an extreme pain to do by hand. Is there any built-in formula or function in Excel that can do this?

Deebaser

This is a job for Access. Do you have that available?

Deebaser

Else what I suppose you could do is pull each student out individually into two columns (Student Name, Score) copy the rows and Paste Special as 'Transpose"

cytorak

Deebaser wrote: »

This is a job for Access. Do you have that available?

I do have Access, but I haven't used it in many, many moons.

Zul the Conqueror

You can do something at least approaching what you're looking for with Pivot Tables. Here's the Wikipedia page, which describes what they do, and links to some tutorials. It'll at least simplify getting the scores into rows, but might not do it outright. Hopefully that helps. If not, you can probably write a macro to do it, but I couldn't give you detailed directions off the top of my head.