Script which could merge two Excel documents

[andy_nic]

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

[limbo]

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).

[srochford]

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.

[andy_nic]

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

[limbo]

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.

[andy_nic]

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.