Need help concatenating fields from different workbooks

[excelhelp14]

Hi there ...
I am trying to take one particular cell from a certain workbook spreadsheet and concatenate the contents to the same cell number in a different workbook spreadsheet.
In order to make sure it is the correct record that I am concatenating to, I need to create a key made up of two seperate cells (firstname + lastname) and match the
record on both workbooks before I concatenate. Please help.

[Special-K]

Can you supply some example data?

[excelhelp14]

Hi..Thanks so much for responding...

Here's an example:
Workbook1 : Final
Column A : Dept #
Column B : Employee Name
Column C : Job Description

Workbook2 : Staff
Column A : Dept #
Column B : Employee Name
Column C : Job Description

So both workbooks are identical as far as columns are concerned. What I want to do is that every time Column C in "Staff" gets updated, I want to update Column C in "Final" as well. BUT column C in "Final" may already have data, so I want to add the new Column C data from "Staff" to the end of the existing Column C data in "Final". I want to make sure I have the correct record before updating so therefore I want to create a unique field using Dept # and Employee Name
And the data is in separate multiple workbooks not separate worksheets in one workbook.

I appreciate your help!

[excelhelp14]

Can anyone help me with this problem? I don't even know if it's possible to extract data from separate workbooks?? I know worksheets within one workbook is doable...but in my problem I have different workbooks that I need to extract data from...please help!!!

[ianh]

you can add reference's in the following format =[Book2]Sheet1!$A$1
where[Book2] would be your file name sheet1! would be the tab name (always with the ! at the end) then the cell.
You should be able to use this style of reference for concatenations or lookups (lookups change the cell reference to a range).

If you dont have all the work books open you may need to use ='\\theta\ihudson$\[Book1.xlsx]Sheet1'!$A$2. the expanded fist part \\theta\ihudson$ being a file location ie c:\documents\

[excelhelp14]

Anybody have any ideas on how to right the code??

[excelhelp14]

I really need help with writing the code, so if anyone can assist I look forward to hearing from you ... Thanks so much!

[arlu1201]

Why dont you attach your 2 sample workbooks?

[excelhelp14]

Here is an example:
Master.xls
Employee. Tax id. Name. Comments
AB. 1234. Jones, Edward. 8/14 some notes.
CD. 5544. Smith, S/ABC. Some notes.

EmployeeAB.xls
Employee. Tax Id Name. Comments
AB. 1234. Jones, Edward. More notes.

EmployeeCD.xls
Employee. Tax id. Name. Comments
CD. 5544. Smith,S/ABC. Another note.
CD. 5000. Smith,S/ABC. New notes


When the macro is complete, master.xls should look like this:
Employee. Tax Id. Name. Comments
AB. 1234. Jones, Edward. 8/14 some notes. More notes.
CD. 5544. Smith,S/ABC. Some notes. Another note.

The 2nd record in EmployeeCD is ignored because the tax id and name did not match.

Any help is much appreciated...

[arlu1201]

The 2nd record in EmployeeCD is ignored because the tax id and name did not match.

What if there is a record in employeeAB which is not there in master.xls, should it be ignored as well?

[excelhelp14]

yes it will be ignored as well .. any records that do not have an exact match are just ignored. I am combining the tax id and name, to get a unique key.

[excelhelp14]

no ideas for me??

[TMS]

I don't think anyone wants to create two sample workbooks in order to develop and test formulae and/or code for you.

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook. Don't upload a picture when you have a workbook question. None of us is inclined to recreate your data. Upload the workbook and manually add an 'after' situation so that we can see what you expect. In addition clearly explain how you get the results..
To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'.
To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.

On this page, below the message box, you will find a button labelled 'Manage Attachments'.
Clicking this button will open a new window for uploading attachments.

You can upload an attachment either from your computer or from another URL by using the appropriate box on this page.
Alternatively you can click the Attachment Icon to open this page.

To upload a file from your computer, click the 'Browse' button and locate the file.

To upload a file from another URL, enter the full URL for the file in the second box on this page.
Once you have completed one of the boxes, click 'Upload'.

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[excelhelp14]

Thanks so much for the etiquette tips...I have never used this site before, so I appreciate it. I couldn't figure out how to attach the attachments, thats why I added it onto the thread. I still can't
find the manage attachments that you are talking about...

[TMS]

If you click on "Go Advanced", the Attachments icon looks a bit like a paperclip on the top row of icon (between the smileys and the Undo icons.


Regards, TMS

[excelhelp14]

Ok, I added attachments of the three files I am working with.

The final outcome for Master should be:
Employee Tax ID Name Comments
AB 1234 Jones, Edward 8/14 some notes. More notes.
CD 5544 Smith, S/ABC Some notes. Another note.

Thanks so much!