Help for Newbie, please and thank you!

[dankellie]

column A has 3,600 names (last, first), column B is the name of school for each name (30 schools), columns C,D,E,F and G are numbered scores for each of the names.

column I has 600 names (last, first), column J has lettered grades for different level (15 levels).

My problem: I want the names from column I compared to column A, giving me (in new columns) the 600 names with their applicable scores from columns C,D,E,F and G, sorted by column J (the letter grade).


My story...My wife is a physical eduction teacher responsible for 600 students from 30 different schools. There are 3,600 students in the test program. She has a list (converted to excel) of all 3,600 student with the appropriate scores. However, she needs the 600 names (covering the 30 schools) with their respective scores sorted by their 15 different levels. We don't need the name of the school (column B).

I laughed at myself last night as we were cutting and pasting all the different names to get them in multiple columns when it dawned on me there must be an easier way. Can anyone help us? I am new to excel so if you can be as specific as possible I would appreciate it.

Also, will we run into any problems if a student has his first name listed as Robert in one column but Rob in another with the same last name?

Thanks in advance.

 

[eliW]

Hi dankellie and welcome to the board,
If each name in column I is identical to a name in column A then you can use VLOOKUP formula to compare and bring the desired information like this:

In K2 enter: =VLOOKUP($I2,$A$2:$G$3600,COLUMN()-8,0)

Then copy by dragging the formula until O2
Then highlight K2:O2 and copy down 600 rows to the last name.

Eli

 

[dankellie]

WOW, worked great but ...

Is there anyway I can account for names that aren't identical? For example; some of the names in the 3,600 list contain shortened names such as Pam instead of the more formal name Pamela (which I have on my list of 600) while others include the middle initial of their name. Any way of getting around this?

Thanks,

dan & kellie