Changing two way tables into one way tables

[Guest]

Hello - I need to be able to 'undo' a two way table. The data is in this
format:
Student number English Grade Maths Grade
US456 A B+
US455 A- C+

And I need it to look like this:
US456 English Grade A
US456 Maths Grade B+
US455 English Grade A-
US455 Maths Grade C+

So each grade has a separate row - other than copying and pasting hundreds
of records is there a simple way to do this?

 

[Ignacio Machin \( .NET/ C# MVP \)]

=A2&" "&$B$1&" "&B2
where : A2 = "First Student in your table"; $B$1="English Grade" and
B2="English grade for first student and drag this cell untill end then do
the same with $C$1 for theMath Grade
Alejandro

 

[Guest]

Thank you for this, I hadn't thought of doing it that way! The only thing is
I have about 20 columns with different headings and the number of columns
varies on each spreadsheet (some spreadsheets contain up to 400 student
records). This requires that I drag the formulas down the correct number of
cells then change slightly and repeat - is there a way to get excel to do
more of this for me!! Thanks again!

 

[Debra Dalgleish]

To reorganize the data, you can use the 'unpivot' technique described by
John Walkenbach:

http://j-walk.com/ss/excel/usertips/tip068.htm

 

[Guest]

That is BRILLIANT - thanks so much you have saved hours of my life!!!

 

[Debra Dalgleish]

You're welcome! Thanks for letting me know that it helped.