+ Post New Thread
Results 1 to 6 of 6
Scripts Thread, Script which could merge two Excel documents in Coding and Web Development; Hey People Iím looking for a script which could merge two Excel documents but the lines donít match. I have ...
  1. #1
    andy_nic's Avatar
    Join Date
    Jun 2008
    Location
    Peterborough
    Posts
    445
    Thank Post
    31
    Thanked 39 Times in 33 Posts
    Rep Power
    19

    Script which could merge two Excel documents

    Hey People

    Iím looking for a script which could merge two Excel documents but the lines donít match. I have a kaleidos username and password list. This contains forename, family name and first part of the address. So Iím hoping to do Sims report to pull out full address and forename and surname then I want to match the 2 up. So I can do a mail merge.

    Help

    Andy

  2. #2
    limbo's Avatar
    Join Date
    Aug 2005
    Location
    Birmingham
    Posts
    460
    Thank Post
    2
    Thanked 41 Times in 36 Posts
    Rep Power
    25
    Your bet bet would be to use a look up table (unless you are comfortable with MSAccess - in which case this would be by far the best option)

    You basically put each set of data into a different sheet in the same workbook. You then lookup the data in one sheet from the other using either the vlookup or hlookup function.

    Search for hlookup or vlookup and you should find a good tutorial.

    Couple of things to consider - far easier if you are just comparing the data in one cell, so first combining name and address into one long cell will make the job easier. Also the table you are looking up needs to be sorted into alphabetical order with the first column being the value you are looking up (so in this case probably the combined string of name and address).

  3. #3

    Join Date
    Aug 2005
    Location
    London
    Posts
    3,154
    Thank Post
    114
    Thanked 527 Times in 450 Posts
    Blog Entries
    2
    Rep Power
    123
    Not sure I understand exactly what you've got but I suspect you need to do a vlookup to find out info from one list and add it to another. This needs one field in both tables to be the same (typically you'll have the student ID in both sets of data)

    If this doesn't make sense then can you post some example data (anonymised!) then I can show you what I mean.

  4. Thanks to srochford from:

    andy_nic (23rd April 2010)

  5. #4
    andy_nic's Avatar
    Join Date
    Jun 2008
    Location
    Peterborough
    Posts
    445
    Thank Post
    31
    Thanked 39 Times in 33 Posts
    Rep Power
    19
    The data has no real unique ID
    So let’s say I have the KLP list
    Forename, Surname, Address
    Bob, Smith, 101 Fake Street

    Sims report
    Forename, Surname, Address
    Bob, smith, 101 fake street Peterborough pe5 h88
    However there can be more than 1 bob smith

  6. #5
    limbo's Avatar
    Join Date
    Aug 2005
    Location
    Birmingham
    Posts
    460
    Thank Post
    2
    Thanked 41 Times in 36 Posts
    Rep Power
    25
    Put an extra column in each table (will need to be the first column) that takes the values from two or three columns and strings them together - so bob smith who lives at 101 Fake Street would have a cell that reads bob_smith_101_Fake_Street - that will then be a single unique value you can use to look up because it will not have the same value as bob_smith_84_Another_street.

  7. Thanks to limbo from:

    andy_nic (23rd April 2010)

  8. #6
    andy_nic's Avatar
    Join Date
    Jun 2008
    Location
    Peterborough
    Posts
    445
    Thank Post
    31
    Thanked 39 Times in 33 Posts
    Rep Power
    19
    Thanks people,
    Only got what you meant at 7 last night. I did a Concatenate to merge the first name, last name and first 8 characters from the left of the address on both spread sheets then used Vlookup for find the whole address. I was thinking when I first thought about it was a VB script which could read each line then check certain fields to see if they matched, Then copied over the full address field.

SHARE:
+ Post New Thread

Similar Threads

  1. Flash Embedded in Excel Documents
    By BKGarry in forum How do you do....it?
    Replies: 1
    Last Post: 2nd February 2010, 06:43 PM
  2. [MS Office - 2003] Excel printing documents twice!
    By TheFopp in forum Office Software
    Replies: 6
    Last Post: 25th February 2009, 10:04 PM
  3. My Documents script not working on R2
    By contink in forum Scripts
    Replies: 15
    Last Post: 11th June 2008, 10:40 AM
  4. Replies: 21
    Last Post: 29th December 2007, 10:53 PM
  5. Script to Create My Documents Subject Folders
    By SwedishChef in forum Scripts
    Replies: 15
    Last Post: 5th July 2006, 08:14 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
  •