Trouble Aligning Matching Cells & their associated values

Hey all, I'm trying to work on some analysis but before I begin, I need to make my Excel spreadsheet align matching values & their associated values. Currently my spreadsheet looks partly like this:

SEQN GENDER RACE AGE SEQN HDL SEQN LDL SEQN PHYTO SEQN PSA
31127 1 3 0 31128 55 31130 31133 10.6 31132 2.25
31128 2 4 11 31129 46 31131 49 31145 17.9 31134 2.15
31129 1 4 15 31130 31132 75 31146 31150 1.01
31130 2 3 85 31131 39 31133 81 31150 351 31154 0.91
31131 2 4 44 31132 59 31134 98 31157 11.7 31158 1.83
31132 1 3 70 31133 54 31139 31161 17.4 31164 0.97
31133 2 4 16 31134 49 31141 31171 29.8 31175 5.61

But I want it to look like this:
SEQN GENDER RACE AGE SEQN HDL SEQN LDL SEQN PHYTO SEQN PSA
31127 1 3 0 _____ __ _____ __ _____ ___ _____ ___
31128 2 4 11 31128 55 _____ __ _____ ___ _____ ___
31129 1 4 15 31129 46 _____ __ _____ ___ _____ ___
31130 2 3 85 31130 31130 _____ ___ _____ ___
31131 2 4 44 31131 39 31131 49 _____ ___ _____ ___
31132 1 3 70 31132 59 _____ __ _____ ___ _____ ___
31133 2 4 16 31133 54 31133 81 31131 10.6 _____ ___

& this is only a part of the entire spreadsheet, there are 10349 rows. I've attached a copy of the spreadsheet. Any help will be greatly appreciated.

One way to do it is to copy the first 4 columns into Sheet2, and then copy the headings from E1:L1 of Sheet1 into Sheet2. Then put this formula in E2 of Sheet2:

=IFERROR(VLOOKUP($A2,Sheet1!E:F,1,0),"")

and this in F2:

=IFERROR(VLOOKUP($A2,Sheet1!E:F,2,0),"")

Note these are very similar - the only difference is the 3rd parameter of the VLOOKUP term. Then you can copy E2:F2 into G2, then I2 and K2. Then copy all the formulae in E2:L2 down as far as you need. Once it has calculated (took a few seconds on my machine) you can fix the values and you're done. And you still have the original data in Sheet1 if you need it.

Hope this helps.

Pete

Thanks, I will try this tonight

I'm using Excel '97-'03 & when I enter the formulas, I get #NAME? error in the E2 & F2 cells. What did I do wrong?

You didn't do anything wrong - I didn't realise you were using XL2003. You need to change the formula to this in E2:

=IF(ISNA(MATCH($A2,Sheet1!E:E,0)),"",VLOOKUP($A2,Sheet1!E:F,1,0))

and to this in F2:

=IF(ISNA(MATCH($A2,Sheet1!E:E,0)),"",VLOOKUP($A2,Sheet1!E:F,2,0))

and then follow the instructions as before.

Hope this helps.

Pete

It worked perfectly, I thank you greatly

Glad to hear it.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete