Link Spreadsheets - Merge Data

[Keeper4826]

I have two spreadsheets with similar data (Last Name, First Name, User ID). One spreadsheet has the two name fields separated into two columns, while the other has them combined into one (Lastname, Firstname). The first spreadsheet doesn't have the UserID field populated. I need to compare the records between the two spreadsheets and update the UserID from SS2 to SS1 where a match is found.

• How do I link the two spreadsheets?
• How do I perform the comparison between the first and last name fields, being that they are record in two columns in SS1 and a single column in SS2?
• When a match is found between spreadsheets, how do I copy the UserID from SS2 to SS1?

I have not scripted Excel before, so this is a totally foreign task for me. Thanks.

[Carim]

Hi,

Take a look at the Vlookup() function in Help ...F1
It should fix your problem ...

HTH
Carim

[Keeper4826]

This looks like a great place to start, if I can figure out how to perform it between two different spreadsheets. However, it doesn't clear the hurdle of comparing names found in one column in SS2 and two columns in SS1.

[VBA Noob]

Hi,

Try this.

http://cjoint.com/?lgvq5hevBS

Had to add a new column to sheet two which you can hide to join the first name and last name for the search

VBA Noob

[Keeper4826]

Thanks. I was acutally toying with combining the two column into one, and then doing a comparison: =CONCATENATE(A1,", ",B1).

I think I may need to copy the worksheet of SS2 into SS1 so I can do the VLOOKUP.

In my formula, can I combine functions to have the conatenation and the VLOOKUP in the same string?

[VBA Noob]

Hi,

I used this to change the Last name, First Name into First Name Last Name e.g Jones, Jack to Jack Jones.

=VLOOKUP(TRIM(RIGHT(A2,LEN(A2)-FIND(" ",A2))&" "&LEFT(A2,FIND(" ",A2)-2)),Sheet2!A1:D3,4,0)

So looks like you want to look up last name first name (Seperate Columns) to Last Name, First Name column. Then try this

=VLOOKUP(TRIM(A2&", "&B2),Sheet1!A1:B3,2,0)

A2 = Last Name
B2 = First Name
&", "& inserts space and a comma


VBA Noob

[Keeper4826]

The range of reference has the names combined into one column (lastname, first). The range being searched for (the names I need to find) has the names separated into to columns.

So I want to take a two column name and search for in one column.

I'm working with your suggestions so see if I can understand them and apply to them to my actual data. Thanks for continuing to assist!

[Keeper4826]

I'm still wrapping my head around the code - but I see a potential issue. Is this function case sensative? Often times, the case varies widely between the two spreadsheets.

The last argument in your code is 0. Why 0 instead of False?

[VBA Noob]

It's not case sensitive.

If your return is text you could add Proper.

=PROPER(VLOOKUP(TRIM(A2&", "&B2),Sheet1!A1:B3,2,0))

If you want your search value to say return upper then you could use this but as I say there's no need

=PROPER(VLOOKUP(UPPER(TRIM(A2&", "&B2)),Sheet1!A1:B3,2,0))

VBA Noob

[Keeper4826]

OK, I'm tearing my hair out.

I've figured out the code completely. Not using PROPER. I found a record that would test true and entered the code, expecting it to return the UserID. Here's the code that I used:

=VLOOKUP(TRIM(C2&", "&B2),ITIM_NoEmployeeIDEmployee!A2:B1059,2,FALSE)

This failed to work. It returned the following: #N/A

I figured I would simplify by creating a new spreadsheet with only the one record for each worksheet. I entered the record forwhich I was trying to populate, and boom, it worked. Why, I don't know? The only difference in the code was the fact that it pointed to a different spreadsheet (which solved one of my initial problems). It looked as follows:

=VLOOKUP(TRIM(C2&", "&B2),[PS_ITIM_Comp.xls]ITIM_NoEmployeeIDEmployee!A2:B1059,2,FALSE)

So why does this not work in the original file? (Note that the case sensitivity is not tested in this example, as the match on both worksheets.

[Keeper4826]

I stepped through the formula evaluation to see exactly what it was doing, and everything looks perfect. There is litterly no difference beetween the original - where it is being evaluated worksheet to worksheet, and the new one - where it is evaluated spreadsheet to spreadsheet. I don't get it?

In the new version, where only the one record exists, I compeleted the recreation and changed remove the redirection to the outside file. Bang, meltdown. It stopped working...

[VBA Noob]

Hi,

The problem with using a join in the find criteria means the data needs to be accurate e.g The below looks ok but only one will give you a return. That's why I suggested in my first post to add a column to do the join and then hide it

Jones, Jack
Jones , Jack
Jones, Jack

If you post the file someone will take a look for you

VBA Noob

[Keeper4826]

Here's the test file. I re-wrote the function and now it partially works. I threw in a record which I expect to fail, and it did, but there are still good records which don't work.

When I cut an paste the re-written function into the cell for the record which is working, on the original spreadsheet, it no longer works.

[VBA Noob]

Hi,

You forget to lock down the search range with $ signs.

Change the formula to and it should work

=VLOOKUP(TRIM(B2&", "&A2),ITIM_NoEmployeeIDEmployee!$A$2:$B$1059,2,FALSE)

Won't find JAN ESPEN FYLLING as Espen, Jan in the other sheet

VBA Noob

[Keeper4826]

That solved my problem. Thanks for all your help!

[VBA Noob]

No Problem

VBA Noob