need help with complicated excel problem:

Discussion in 'BlackHat Lounge' started by outsidethesquare, May 2, 2012.

  1. outsidethesquare

    outsidethesquare Regular Member

    Sep 9, 2009
    Likes Received:
    Hi everyone.

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

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

    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:
    yyy code3 code4 code7
    prereq other codexx codeyy
    another pre anothercode ... ...

    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...