MetaChat - Excel question:

MetaChat is an informal place for MeFites to touch base and post, discuss and chatter about topics that may not belong on MetaFilter. Questions? Check the FAQ. Please note: This is important.

03 September 2009

Excel question: →[More:]I have a column of 20 different numerical codes. Each code represents a person. Each code appears between 4 and 15 times on the spreadsheet. I'd like to add the last names attached to each code in a separate column. How do I do this easily? (I don't want to sort by codes for other reasons that are too complicated to explain.)

For example:

1234
2345
3456
4567
2345
4567
2345
3456

would become

Smith | 1234
Jones | 2345
Mouse | 3456
Santa | 4567
Jones | 2345
Santa | 4567
Jones | 2345
Mouse | 3456

Thanks!

post by: mudpuppie at: 11:07 | 3 comments

Put the codes and names on a new sheet (where the codes are sorted numerically) and use @VLOOKUP on the codes to find the names.

posted by dhartung 03 September | 12:07

First make a table of key:value pairs.... ie 1234 smith in A1 and B1, and so on down (no need to sort or anything)

then, presuming your numbers are in column D and you want results in column F, F1 becomes:

=vlookup(D1,A:B,2,0) & " | " & D1

posted by pompomtom 03 September | 22:23

This is probably a moot point by now, but a hack that might work that looks less scary than the actual solutions proposed above:

Open a new Excel worksheet. In column 1, fill in a series of sequential numbers. In column 2, copy and paste your codes from the first sheet. Now sort this new sheet by codes, and fill in the last names. Now resort this sheet by your first, sequentially-numbered column. Now copy and paste the column of last names, which should now be in the same order as on the original sheet, into your original sheet.

posted by occhiblu 04 September | 14:48

← The MetaChat Movie of the Month for September || End of Summer Lyrics Challenge! →

HOME || REGISTER || LOGIN