Excel sorting questions

Question

Ok I have two files in Excel 2007. They are both to large to do this sort manually consisting of several thousand lines.

File 1 has first names and ID numbers as 2 columns: Joe 1 Mark 2 Bob 3 Sally 4 etc...

File 2 has last names and ID number as 2 columns: Smith 1 Johnson 2 Brown 3 Hands 4

Is there an easy way where I can sort these two lists in excel so that I have a file of columns that says Joe Smith 1 Mark Johnson 2 etc

Thoughts? If this can't easily be done with excel other options i guess would be python.

Answer 1

Do you want to perform this operation often? In that case, I would suggest writing a macro. (Not sure as to how it would read from 2 files though)

If it's a one time thing, you could do it manually.
If you know that both files contain exactly the same ID numbers, try this:

• In both files, sort all data by ID number, using Excels custom sort.
• Copy the column containing the last names and past it next to the column with the first names.

If necessary, you can copy both columns (ID and last name) and compare if the two ID-numbers are equal. You can use filter to see which entries are invalid.

Alternative
If not all ID numbers are equal, you can use VLookup

• Copy the info from both files into a new file.
  Lets assume that A:A contains ID numbers from the first file, B:B the corresponding first names. C:C contains ID numbers from the second file and D:D the corresponding last names.
• make a new column with all ID numbers (E:E)
• now let F1 contain VLookup($E1;A:B;2;FALSE), let G1 contain VLookup($E1;C:D;2;FALSE) and fill out the entire columns.

You might want to specify the behavior of missing names by wrapping an IF test around the VLookup replacing #N/A by a default name or an empty string.