combining 18 columns into 5 columns

[outumbr]

hi
I am devising a school marksheet in excel
I have 21 subjects including maths French and english which are mandatory subjects. all tudents do maths English and French . hence 18 columns with different subjects left .only 5columns out of the 18 are filled as there are many options and a student does only 8 subjects
what I want to do is to fill in the marks in only 5 columns and write the subject beside the marks in another colum.
please help

 

[Rick Rothstein]

I find your question to be a little bit sketchy. Is Column A the student's name? Do you have 18 or 21 (with three pre-marked) columns following that? What type of mark are supposed to go into the columns (perhaps the letter X)? I don't understand what your output should look like nor where it should be located at... can you describe that in a little more detail?

 

[outumbr]

Thanks Rick
col A contains the name of the student
col B contains marks for English
col C marks for French
Col D marks for Maths
Col B C and D are mandatory that is if I have 15 students all of the 15 students have to do English French and Maths
from col E to col V I have 18 subjects
these subjects are optional that is a student can choose any 5 subjects from the remaining 18 subjects
[TABLE="width: 713"]
<colgroup><col width="110" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4022;"> <col width="40" style="width: 30pt; mso-width-source: userset; mso-width-alt: 1462;" span="21"> <tbody>[TR]
[TD="class: xl65, width: 110, bgcolor: transparent"]NAME[/TD]
[TD="class: xl65, width: 40, bgcolor: transparent"]ENGLISH[/TD]
[TD="class: xl65, width: 40, bgcolor: transparent"]FRENCH [/TD]
[TD="class: xl65, width: 40, bgcolor: transparent"]MATHS[/TD]
[TD="class: xl65, width: 40, bgcolor: transparent"]ADD MATHS[/TD]
[TD="class: xl65, width: 40, bgcolor: transparent"]ACCOUNTS[/TD]
[TD="class: xl65, width: 40, bgcolor: transparent"]ECONOMICS[/TD]
[TD="class: xl65, width: 40, bgcolor: transparent"]COMPUTER[/TD]
[TD="class: xl65, width: 40, bgcolor: transparent"]PHYSICS[/TD]
[TD="class: xl65, width: 40, bgcolor: transparent"]BIOLOGY[/TD]
[TD="class: xl65, width: 40, bgcolor: transparent"]CHEMISTRY[/TD]
[TD="class: xl65, width: 40, bgcolor: transparent"]DC[/TD]
[TD="class: xl65, width: 40, bgcolor: transparent"]DT[/TD]
[TD="class: xl65, width: 40, bgcolor: transparent"]ENTERPRISE[/TD]
[TD="class: xl65, width: 40, bgcolor: transparent"]TRAVEL N TOURISN[/TD]
[TD="class: xl65, width: 40, bgcolor: transparent"]AL[/TD]
[TD="class: xl65, width: 40, bgcolor: transparent"]ART[/TD]
[TD="class: xl65, width: 40, bgcolor: transparent"]FRENCH LIT[/TD]
[TD="class: xl65, width: 40, bgcolor: transparent"]ENGLISH LIT[/TD]
[TD="class: xl65, width: 40, bgcolor: transparent"]SOCIOLOGY[/TD]
[TD="class: xl65, width: 40, bgcolor: transparent"]PHYSICAL EDUCATION[/TD]
[TD="class: xl65, width: 40, bgcolor: transparent"]BUSINESS ST[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]BHOOBUN Ivans Madun[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]45[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]55[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]40[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]60[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, align: right"]10[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]50[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]72[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]78[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]BHUDDOO Anikesh Anand[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]56[/TD]
[TD="class: xl67, align: right"]25[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]46[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]84[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, align: right"]15[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]40[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, align: right"]0[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, align: right"]25[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]BUNDHOO Thanveersingh[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]49[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]62[/TD]
[TD="class: xl67, align: right"]34[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]72[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]40[/TD]
[TD="class: xl67, align: right"]20[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]62[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]50[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, align: right"]31[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]BURKATALLY Fardeen[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]51[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]52[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]62[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]81[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, align: right"]25[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]52[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]70[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]50[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]CHAMPA Heeman[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]56[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]61[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]54[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, align: right"]30[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, align: right"]34[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]46[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]40[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]57[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]50[/TD]
[/TR]
</tbody>[/TABLE]

what I am planning to do is to have a for mat as follows
col A name of student
col B marks for English
col C marks for French
col D marks for Maths
col E subject 1 name
col F subject 1 marks
col G subject 2 name
col H subject 2 marks
and so on up subject 5 name and subject 5 marks

[TABLE="width: 632"]
<colgroup><col width="64" style="width: 48pt;" span="3"> <col width="74" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2706;"> <col width="64" style="width: 48pt;" span="9"> <tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]ENGLISH[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]FRENCH [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]MATHS[/TD]
[TD="class: xl67, width: 74, bgcolor: transparent"]subject 1 name [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]subject 1 marks[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]subject 2 name[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]subject 2 marks[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]subject3 name [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]subject 3 marks[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]subject 4 name[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]subject 4 marks[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]subject 5 name [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]subject 5 marks[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]45[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]55[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]40[/TD]
[TD="class: xl68, bgcolor: transparent"]add maths [/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]60[/TD]
[TD="class: xl68, bgcolor: transparent"]computer[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl68, bgcolor: transparent"]physics[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]50[/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]56[/TD]
[TD="class: xl66, align: right"]25[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]46[/TD]
[TD="class: xl68, bgcolor: transparent"]add maths [/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]84[/TD]
[TD="class: xl68, bgcolor: transparent"]computer[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl68, bgcolor: transparent"]physics[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]40[/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]49[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]62[/TD]
[TD="class: xl66, align: right"]34[/TD]
[TD="class: xl68, bgcolor: transparent"]accounts [/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]72[/TD]
[TD="class: xl68, bgcolor: transparent"]economics[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]40[/TD]
[TD="class: xl68, bgcolor: transparent"]computer[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]51[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]52[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]62[/TD]
[TD="class: xl68, bgcolor: transparent"]add maths [/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]81[/TD]
[TD="class: xl68, bgcolor: transparent"]economics[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]25[/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]56[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]61[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]54[/TD]
[TD="class: xl68, bgcolor: transparent"]computer [/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]30[/TD]
[TD="class: xl68, bgcolor: transparent"]traveln tou[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]34[/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]