Concatewhat?

July 7, 2009 8:50 AM

I need to merge two Excel sheets with some common fields, but the most advanced formula I've ever used is Autosum.

I have two worksheets. One has a list of people that I've invited to an event. The other has a much larger list of people, including the people on the 1st sheet, which has a field for phone numbers. I'd like to bring the phone numbers from the 2nd sheet into the 1st sheet, but only for the people who are on the 1st sheet. Both sheets have "First Name" and "Last Name."

I've been googling around and have found things about vlookup and concatenating (same thing?). I've never used vlookup before, so I guess I'd need a basic primer in vlookup and/or how to use it in this specific instance to achieve what I want. Unless there's a better solution. I've also read about maybe pulling the two sheets into Access, but I'd rather not have too complicated an end-product since very tech-unsavvy people will need to be using the results.

On both sheets, Last and First name are columns A and B, and phone numbers on the second sheet are columns D, E, and F.

posted by thebazilist to computers & internet (9 comments total)

This should be a relatively easy task, and you are on the right track. I am by no means an Excel expert but I have to do these types of tasks quite a bit.

To my knowledge, vlookup only works on a single field, so you would need to first compress 'first and last name' into a single 'name' field.

Assuming our final worksheet will be:

Column A - First Name
Column B - Last Name
Column C - Full Name
Column D - Phone #

In column C, row 2, you would use the formula " =A2&' '&B2". This will return the contents of Column A followed by a space, followed by the last name. Copy this cell to all rows in column C. Copy all of column C, right-click and choose 'paste special'. Choose to paste the 'value'. This will overwrite the function with the actual combined name.

Now, repeat these same exact steps in your larger worksheet so you have a single column that should match back to the first worksheet. Make sure this 'full name' column comes BEFORE your phone number column.

Now, in column D of your first worksheet, you'll want to use VLOOKUP. I may not explain this well, so taking a look at the help for this function may be of assistance. Since you're working with two sheets, it's easier to start the function and then physically choose the cells you want to work with, rather than typing in the entire function.

In cell D2, type "=VLOOKUP(". This will tell excel you're starting the function. Use the arrow keys or mouse to then select cell C2 (full name). This tells the function what you want to find a match for. Once that's selected, type in a comma, then switch to your other worksheet and select the entire full name column AND any other columns UP TO the phone number column. Press comma again, then enter the position of the phone number column. (numbering starts with 1, and that would be your full name field.) Finally, press comma again and type in 'false'.

A properly formatted function may look similar to the following:

=VLOOKUP(C2,WorkSheetName!D:E,2,false)

That would match on cell C2 of the current worksheet, then look at the sheet named "WorkSheetName", columns D and E. In my fake example here I would have the full name in column D, phone number in E. VLOOKUP, as far as I know, always looks for a match in the first column identified. The '2' indicates what data you want returned, in this case, the second column, or whatever is in column "E" for the matched record. False is what I've always used, I believe it has to do with duplicate records.

Another example:
=VLOOKUP(C2,WorkSheetName!A:E,5,false) - same as above, but full name is in column A, phone number is in column E, so we want this function to return the data from column E, or the fifth column that was selected.

Hope that helps.. if all else fails, look up some more documentation either in Office's Help or just a quick google and you should be able to figure out VLOOKUP.
posted by MarkLark at 10:01 AM on July 7 [1 favorite]

Is this a one-time thing, or something you'll have to do often? If it's a one-shot need, then copy and paste should work just fine.

vlookup and concatenation are not the same thing. vlookup is a formula to find a corresponding value in a table, based on the value you have in the current cell. Concatenation is combining the values in two or more cells.

Are these two sheets in the same workbook, or two entirely different Excel files?
posted by SuperSquirrel at 10:05 AM on July 7

vlookup compares a cell with a value and asks "are they the same". If the answer is yes, it inserts a value. Concatenating is bringing the data from two or more cells into one cell.

Because you probably have more than one person with the same last names or the same first names, you will have to work with both of the formulas to do what you want to do.

- Make sure the two sheets are in the same Excel workbook.
- On Spreadsheet A, use concatenate to create a new column with the full name, i.e. "last name, first name" (or some variation thereof).
- On Spreadsheet B, do the exact same thing in a new column.
- You will now have three columns on Spreadsheet A - Last Name, First Name, Full Name
- On Spreadsheet B, you should have six columns - Last Name, First Name, Full Name, Phone #1, Phone #2, Phone #3
- On Spreadsheet B, create a named range for the two columns Full Name, Phone #1. Say that you name it "phone1".
- On sheet A, use a blank column to the right of the "Full Name" column, and use a vlookup formula to compare the Full Name with your named range.
- The lookup value is the Full Name cell. The table array is your named range (i.e. "phone"). The column index number is 2 (because you are looking for the data in the phone number #1 column, which is column 2 in your named range), and the range lookup value should be set to "false" (because it should only be ONE correct answer that matches the name with the phone number)
- So the formula in the first cell on sheet A would be "=VLOOKUP(C2,phone1,2, FALSE)"
- Then just drag fill the formula down to the end of your list on sheet A, and the phone numbers from the Phone #1 column on sheet B should be filled in.
- Copy the phone number column, and" paste special" it over itself as values only, removing the formula and substituting the phone numbers as text/numbers.
- Watch out for duplicate full names, as that will cause it to use only the first instance of a match in your named range
- Since you have additional columns of phone numbers, just make additional named ranges in the same manner, and fill them into blank columns on sheet A.
(You could also concatenate the phone numbers into one column on Sheet B, with a delimiter between them, transfer them over to sheet A in one fell swoop, and then use the Text to Column wizard on Sheet A to split them back out into columns)

That gives you the basic steps, anyway. It sounds complicated, but once you have done it once it's easy. They are good formulas to know - I use them often.
posted by gemmy at 10:09 AM on July 7 [1 favorite]

This is a techy, roundabout way of approaching your Excel problem, but it would be extremely easy to do this with a SQL database. One of the core functions of SQL is the operation JOIN, which looks at corresponding columns in two tables and returns all the data in rows that have matching tuples (cells). This sounds like exactly what you need to do: whenever you see a name match between the two tables, return all the data for those rows (name + phone number).

All the work would be done on your part (rather than your tech-unsavvies) and you could format it however prettily you'd like as an HTML document, which you can turn into a PDF, DOC, or just copypaste somewhere. Assuming your computer isn't a locked-down machine at work, it would take half an hour of your time.

The basic gist of what we would do:
1) Export your Excel data as .csv files (comma separated value)
2) Install WAMP
3) Create a database, import .csv files
4) Make a PHP page to call a join and print the results

I would be able to walk you through it and do the simple coding required if you're still interested. Game, bazilist?
posted by ThatRandomGuy at 10:14 AM on July 7

One thing that might not be immediately obvious about VLOOKUP is that the cell range needs to be sorted by the column you're vlookup'ing.
posted by mhum at 12:54 PM on July 7

One thing that might not be immediately obvious about VLOOKUP is that the cell range needs to be sorted by the column you're vlookup'ing.

This is incorrect

Sorting only matters when the Range_Lookup variable (the last one) is TRUE, to enable 'closest-to' matches. This is helpful for stuff like tax brackets, but is completely the wrong approach to this asker's question.
posted by pompomtom at 4:55 PM on July 7

This is incorrect

D'oh. My mistake. You are absolutely correct. Exact VLOOKUP matches don't require sorted ranges. I guess I just got so used to sorting them that I forgot why I did it.
posted by mhum at 7:41 AM on July 8

MarkLark: I'm going through your instructions, but when I type " =A2&' '&B2" into C2, it says there's an error with my formula. So... I'm kind of stuck there. When I remove one or both of the apostrophes and ampersands, it removes the error but I'm not sure which to remove to make it do what I want it to do.

ThatRandomGuy: I kind of do have a locked computer at work, ie, I'm not allowed to be installing things. Also, the final product needs to be editable (so I can put in "called 7/08" or "attending," etc) so finishing up with a PDF won't really work.
posted by thebazilist at 9:49 AM on July 8

Change the single quotes to double quotes:

   =A2 & " " & B2

posted by SuperSquirrel at 11:15 AM on July 8