Swap Column to Rows

Hi Guys,
I have a list of students with their exam results listed in columns as below:

Student Name : Exam1 Mark : Exam2 Mark : Exam3 Mark : etc...
J Smith 97 95 90

I need to swap them like as below to insert them into a db table:

J Smith : Exam1 : 97
J Smith : Exam2 : 95
J Smith : Exam3 : 90
etc
etc

What is the best way to go about this??

Thanks for your help in advance

Cheers

Robin

yuo need to give us abit more information, what is the maximum number of exams and does it apply to all students, eg has every1 sat 5 exams

regards

Dav

Originally Posted by Dav

yuo need to give us abit more information, what is the maximum number of exams and does it apply to all students, eg has every1 sat 5 exams

regards

Dav

Hi Dav,

There are a total of 50 students. Each student sat 5 exams.

Cheers

Robin

Select it all:

Paste Special & Click [Transpose] on a new Sheet or wherever.

Should be it .

If I understand you correctly it is not a transpose

if the names start in a2 and the grades b2:f2

In the first cell for the name to appear in your new list put
=OFFSET(A$2,TRUNC((ROW(A1)-1)/5),0)

In the cell to the right of it put
=OFFSET(B$1,0,MOD((ROW(A1)-1),5))

and in the cell to the right of that

=OFFSET(B$2,TRUNC((ROW(A1)-1)/5),MOD(ROW(A1)-1,5))

then copy down the 3 columns for you data

Once youhave transformed it you may wish to copy it elsewhere as values to lose the formula


Regards

Dav

Hi Guys,

The people in this forum are so knowledgeable. I am really grateful for your help. Samprince thanks for reminding me about Transpose I had forgotten all about this function. Dav I used your formula, it just worked a treat it was just marvelous; you’re a genius!!

Thanks very much for your help.

Cheers

Robin