+ Post New Thread
Results 1 to 11 of 11
Windows Thread, Merging data in excel in Technical; Hi all, I need to merge the phone numbers in one excel file into a second file or worksheet within ...
  1. #1
    Mr_M_Cox's Avatar
    Join Date
    May 2007
    Location
    Portsmouth
    Posts
    155
    Thank Post
    5
    Thanked 1 Time in 1 Post
    Rep Power
    0

    Merging data in excel

    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

  2. #2
    jsnetman's Avatar
    Join Date
    Oct 2007
    Posts
    887
    Thank Post
    23
    Thanked 134 Times in 126 Posts
    Rep Power
    40
    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.

  3. #3
    Mr_M_Cox's Avatar
    Join Date
    May 2007
    Location
    Portsmouth
    Posts
    155
    Thank Post
    5
    Thanked 1 Time in 1 Post
    Rep Power
    0
    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

  4. #4

    GrumbleDook's Avatar
    Join Date
    Jul 2005
    Location
    Gosport, Hampshire
    Posts
    10,074
    Thank Post
    1,384
    Thanked 1,889 Times in 1,170 Posts
    Blog Entries
    19
    Rep Power
    614
    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

  5. #5
    Mr_M_Cox's Avatar
    Join Date
    May 2007
    Location
    Portsmouth
    Posts
    155
    Thank Post
    5
    Thanked 1 Time in 1 Post
    Rep Power
    0
    Does anyone have a forula they think might work, I am not overly familiar qwith Excel so any help would be great.

  6. #6
    jsnetman's Avatar
    Join Date
    Oct 2007
    Posts
    887
    Thank Post
    23
    Thanked 134 Times in 126 Posts
    Rep Power
    40
    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/
    Last edited by jsnetman; 31st October 2008 at 11:05 AM.

  7. #7
    SteveBentley's Avatar
    Join Date
    Jun 2007
    Location
    Yorkshire
    Posts
    1,455
    Thank Post
    120
    Thanked 264 Times in 190 Posts
    Rep Power
    73
    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

  8. #8

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,523
    Thank Post
    375
    Thanked 677 Times in 551 Posts
    Rep Power
    166
    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.

  9. #9

    Join Date
    Jan 2008
    Posts
    125
    Thank Post
    6
    Thanked 19 Times in 15 Posts
    Rep Power
    18
    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

  10. #10
    Mr_M_Cox's Avatar
    Join Date
    May 2007
    Location
    Portsmouth
    Posts
    155
    Thank Post
    5
    Thanked 1 Time in 1 Post
    Rep Power
    0
    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?

  11. #11

    Join Date
    Jan 2008
    Posts
    125
    Thank Post
    6
    Thanked 19 Times in 15 Posts
    Rep Power
    18
    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!



SHARE:
+ Post New Thread

Similar Threads

  1. Datacable or Excel Data Services?
    By harvy28 in forum Hardware
    Replies: 2
    Last Post: 28th April 2008, 12:32 PM
  2. Excel Assesment Data
    By danIT in forum General Chat
    Replies: 2
    Last Post: 14th February 2008, 03:14 PM
  3. Replies: 11
    Last Post: 12th November 2007, 01:29 PM
  4. Mail Merging
    By bensewell in forum MIS Systems
    Replies: 1
    Last Post: 12th October 2007, 03:23 PM
  5. Merging data from two cmis datasets
    By gskelton in forum MIS Systems
    Replies: 1
    Last Post: 5th July 2007, 11:47 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •