need help with complicated excel problem:

Hi everyone.


I have a list of data that is spread over different columns like so:


[TABLE="class: grid, width: 500"]
[TR]
[TD]Subject[/TD]
[TD]pre req 1[/TD]
[TD]pre req 2[/TD]
[TD]pre req 3[/TD]
[TD]pre req 4[/TD]
[TD]and so on...[/TD]
[/TR]
[TR]
[TD]col a[/TD]
[TD]col b[/TD]
[TD]col c[/TD]
[TD]col d[/TD]
[TD]col e[/TD]
[TD]col f[/TD]
[/TR]
[TR]
[TD]code3[/TD]
[TD]yyy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]code4[/TD]
[TD]yyy[/TD]
[TD]ggg[/TD]
[TD]sdf[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]code5
[/TD]
[TD]aaac[/TD]
[TD]cccc[/TD]
[TD]jddf[/TD]
[TD]hhdsf[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]code6[/TD]
[TD]xzxzcc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]code7[/TD]
[TD]zxxz[/TD]
[TD]yyy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]code8[/TD]
[TD]zxc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]code9[/TD]
[TD]sfgre[/TD]
[TD]sdffd[/TD]
[TD]dfgd[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]code10[/TD]
[TD]rererreg[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]code11[/TD]
[TD]xfdb[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]code12[/TD]
[TD]hty7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]code13[/TD]
[TD]45t3[/TD]
[TD]sdfg[/TD]
[TD]sdfg[/TD]
[TD]sdfg[/TD]
[TD]zcx[/TD]
[/TR]
[TR]
[TD]code14[/TD]
[TD]345[/TD]
[TD]ssdd[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]code15[/TD]
[TD]34345hf[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]code16[/TD]
[TD]j5565[/TD]
[TD]sdfddf[/TD]
[TD]sdffs[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[/TABLE]



In column A is a list of subject codes at a school, and in columns B --> M are their prerequisite subjects.


What I am trying to do is create a list of prerequisite subjects which I have done by copying each column (B - M) into a single column and removing duplicates, but I want the data to be formatted so that instead of having a subject and it's prerequisites listed in the following columns, I will have the column of prerequisite subjects and all of the possible subjects that particular subject leads to in the following coulms.

For example: yyy is a prerequisite for code 3, code4, code7. So my data would look like:
[TABLE="width: 500"]
[TR]
[TD]yyy[/TD]
[TD]code3[/TD]
[TD]code4[/TD]
[TD]code7[/TD]
[/TR]
[TR]
[TD]prereq other[/TD]
[TD]codexx[/TD]
[TD]codeyy[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]another pre[/TD]
[TD]anothercode[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[/TABLE]




I cannot figure out how to write a VLOOKUP that will take all of the COLUMN A values wherever the subject appears in the Column B - M range.


Help me please...