Merging two files together based on col common to both files

**welchs101** · 07-05-2011, 11:10 AM

I am enclosing a file which shoudl help describe what i am trying to do.

I have two files/sheets of data. Really its files but i show each file on a separte sheet for now to keep things simple. I have a 3rd sheet which shows the "merged" data.

i want to use VBA to merge or join the two files based on the Lot-id.

Not sure the best way to approach merging these two files together?

Any ideas?

**TMS** · 07-05-2011, 11:22 AM

See the attached updated example workbook.

Copy data sheet 1 as it is to the new merged file. Add a helper column to data sheet 2 and then add INDEX/MATCH formula to the end of the merged file.

You could record setting it up to make it easily repeatable.

Regards

**welchs101** · 07-05-2011, 12:19 PM

Hi,

I dont think i can use the index functions to accomplish my goals. I forgot to mention that only a single file will be open at any one time so i dont think i can use the index function. Recall, that each datasheet is actually a single file.

**welchs101** · 07-05-2011, 12:55 PM

I have looked online a bit now and i think what i want to do is called "join(merge)" and while i may know a better name for it now i still cant seem to find any vba-code.

Anyone have any ideas?

**TMS** · 07-05-2011, 12:55 PM

You seem to be making your own contstraints:

Assume you have a workbook with the code in it, completely independent.
Open the datasheet1 workbook and copy it to a new merged workbook. Close the original.
Open the datasheet2 workbook and copy the sheet to the new merged workbook. Close the original.
Now, with the the necessary sheets in the new merged workbook, add the Helper column to the datasheet2 (worksheet) and add the formulae to datasheet2 (worksheet).

Convert the formulae to values and you can delete the datasheet2 (worksheet).

You then have a freestanding merged workbook.

Probably a bit of a pain to set up but once it's done, it's done.

Why *do* you only have one workbook open at a time?

Regards

**welchs101** · 07-05-2011, 01:06 PM

hi,

i was opening up one file at a time because i was combining reading in files from a directory one at a time and then closing them. However, this "other" file i want to merge cant be in the same directory as it is different................than the others. So, your suggestion is starting to look better and better.