MetaChat REGISTER   ||   LOGIN   ||   IMAGES ARE OFF   ||   RECENT COMMENTS




artphoto by splunge
artphoto by TheophileEscargot
artphoto by Kronos_to_Earth
artphoto by ethylene

Home

About

Search

Archives

Mecha Wiki

Metachat Eye

Emcee

IRC Channels

IRC FAQ


 RSS


Comment Feed:

RSS

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