Duplicate Rows - Combining and Flipping

  • Thread starter Thread starter Cytorak
  • Start date Start date
C

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?
 
What is it that you actually want to do? (The best approach depends on what your desired end result
is...) Do you want an overall average? Or to produce a report with the class and quarter values
included as headers?

HTH,
Bernie
MS Excel MVP
 
The end result will be to condense each student to one record with all
their grades in one row. After each student only has one record, I
want to get an average grade for each student for each quarter. I
don't necessarily need headers per quarter; I just need the name and
the numbers all in one horizontal row.
 
OK.

Select your column A (with the names) and use Data / Filter then choose Advanced Filter.....
Select "Filter to another location" select G1 as the location, and check "Unique records only"
and click OK. That will create a list of your students, starting in G2 (the data header from A1
should be in G1).

Then in H2, enter the formula
=VLOOKUP(G2,A:C,3,FALSE)

and in I2, array-enter (enter using Ctrl-Shift-Enter) the formula
=IF(COUNTIF($A:$A,$G2)>=COLUMN(B$1),INDEX($C:$C,SMALL(IF($A$2:$A$1000=$G2,ROW($A$2:$A$1000)),COLUMN(B$1))),"")

Copy I2 across the row until you get some blank cells, then copy H2 and the cells that you just
filled down to match your list of students. When you are done, make sure that there is at least one
formula cell returning a blank at the end of every row. Then copy those cells and paste special
values, and you can delete your original data columns.

HTH,
Bernie
MS Excel MVP


The end result will be to condense each student to one record with all
their grades in one row. After each student only has one record, I
want to get an average grade for each student for each quarter. I
don't necessarily need headers per quarter; I just need the name and
the numbers all in one horizontal row.
 
Back
Top