Printable View
Hi all,
I need to merge the phone numbers in one excel file into a second file or worksheet within the same file, which ever is easiest.
Problem is i need to make sure they copy to the same user
so that gary in file one gets garys number from file 2?
Any ideas
I think you can just copy and paste both columns into a new sheet, they should keep the correct order as long as you don't apply any sorting filters before hand.
They are not in the same order in each file tho.
I need something like
= If Sheet2!A2=Sheet1!A2, Sheet1!E2=Sheet2!E2
So if the names match copy the number across
but the fomula wont work and it needs to apply to all colums not just the ones types in the formula
Sounds like you want to use lookup tables. There are a number of handy sites that will explain it (including the MS site) but I had this one pushed to me the other week on a similar thing
Excel - Merge matching rows from Excel tables /lists
Does anyone have a forula they think might work, I am not overly familiar qwith Excel so any help would be great.
It really depends on the structure of your data, a formula cannot be written for you unless you posted an example of the data. As mentioned before you need to be looking at VLOOKUP. Search google for "vlookup tutorials" http://www.timeatlas.com/mos/5_Minut...OKUP_in_Excel/
Personally I think lookup tables in Excel are an abombination, and if you need that level of complexity you should be using a database.
I'd use both excel files as linked tables into an Access database and create a query that pulls the fields you want from each of the tables (spreadsheets) and has a where clause that matches one or more common fields from both tables.
But that's just me :)
Totally agree about using a database (Access) over a spreadsheet (excel). Only problem I've got is Windows Offline files doesn't like them, nor does most of the people I work with. Very few people prefers a database over a spreadsheet. Despite the fact it's better in the long run.
I would prob sort them by name then c'n'p or better yet import into a database.
I'll assume you have two worksheets in the same workbook, one with a full list of names in column A and phone numbers in column B, the other with just some of the names in column A and no phone numbers. The following will only work if the names are exactly the same in both files, so "B Smith" and "B. Smith" won't work. (Having said that, you'll soon see which ones are a problem and can fix them manually).
- Sort the first sheet alphabetically by column A (obviously selecting both columns so phone numbers remain associated with people).
- Click in cell B1 in the second file and insert a function - VLOOKUP.
- Click in the box for "Lookup_value" and click on cell A1.
- Click in the box for "Table_Array", move to sheet 1 and select all the names and phone numbers in columns A and B.
- Press F4 to make this an absolute cell reference (it won't move around when you drag the formula in a minute). Dollar signs should appear on the reference.
- In "Col_index_num" type "2". (The second column in the "table" on sheet one is the one with the answer in).
- In "Range_lookup" type "FALSE". This makes sure only exact matches work.
- Click "OK".
- Drag the formula you have made in B1 all the way down column B.
Hope that helps.
Simon
I will do my best to explain my data structure a little better but I cannot reveal any of my live data, sorry.
I have one Excel file with the following headings -
First Name Logon Name Last Name Department City Street State Country Company Zip Code Office Business Phone otherTelephone Description Title
And another Excel spread sheet with the following headings -
Employee Number Surname Initial Forename Title Extension Department Building Department Number
I need to add the extention column from the second sheet into the first sheet but making sure it stays with the right names.
So the result would be that John Smith for exapmle in sheet one has the phone number from John Smith in sheet 2.
FYI they are not into same order so cant just copy and paste, there are entries in sheet one that are not in sheet2.
Does that make it any clearer?
The method I described will work, with the following amendments:
- I'd start by putting the two sheets into one workbook with a simple copy and paste - it makes the references easier.
- I'd create a new column in each sheet called FullName, which you can then use to perform the VLOOKUP exactly as I described. You can make FullName by "concatenating" the first name and surname fields.
- Go to the first empty column (say column M) and click in the top cell.
- Type = then click on the first name, type the ampersand character, " ", ampersand and click on the surname. Press enter. e.g. =A1&" "&B1
- Drag this down the column.
- Do the same in the other sheet.
Perhaps the easiest way to just make this work would be (in the sheet with the extensions in) to then copy the new column and Paste Special "values" into a new column. You can then delete all the other columns except FullName (the new values only one) and Extension (which you will probably have to copy and paste) so that you have FullName in column A and Extension in column B.
(You could leave all the other data there, but then you have to be careful how you select the table and choose the column index number).
Good luck!