Combining Two Excel Files into One

Hi, I have two excel files, one has ~2100 rows of data the other has ~3500. The two files have two common columns, one is the year and the other is a unique identification number. I need to combine the data from the file with 2100 records with records from the second file and I need to match them based on their unique identification number and year. The catch is, not all of the data on the 3500 record file is obviously going to go onto the 2100 record file and not all of the data in the 2100 record file has an analog in the 3500 record file. I am hoping there is a way to do this easily as I have already spent 2 days to match 350 records and I'm hoping this isn't going to take me another 10 days to finish! Any help or suggestions would be great!

The file names are "Individual Data.xlsx" and "Chopped Nest Success Data.xlsx". The column headings in the "individual data" file are "State"A1, "Ind_ID" B1, "Year" C1, "Terr_ID" D1, "Age" E1, and "***" F1. The column headings for the chopped data are "State" A1, "Terr_ID" B1, "Year" C1, "NA" D1, "NF" E1, "CH" F1, "CS" G1, "PEH" H1, and "PCF" I1. I need to add the "NA", "NF", "CH", "CS", "PEH", and "PCF" columns to the file with the "individual data" based on the "Year" and "Terr_ID" columns. The state columns would also match up but that is not important as the "Terr_ID" is a unique identifier that must be matched with the year.

Ok, I gave that a try. When I ran it I got a "subscript out of range" error at "Set wi = Workbooks("Individual Data").Worksheets("Sheet1")" I haven't had a VB class in 6 or 7 years so I do not remember what that means. I tried changing the "Individual Data" line to the full location and that did not help. I really appreciate all this help so far and I'm going to continue to try and figure out what's going on with the code here in the meantime but I have a feeling I'm going to need some more guidance on this issue.

I actually checked that and for the "Individual Data" file it is still "Sheet1" but for the second file it was not so I changed it. However, that did not fix the error.

Now I am getting a "Compile Error: Control Variable already in use". When it gives me this it highlights "Sub Bbraden():" in yellow and "For j = j To" in blue as if I had selected it which I did not. I was able to see that it sounds like it has something to do with a loop but that's as far as I could figure out. I really appreciate this help!

So now a box pops up that requests a macro name so I just made one up and it gave me a new start and end sub, what do I do now and is this what I should be seeing?

Well I was confused by the "Type "Option Explicit" then paste the code into the white space on the right " in the first message as I had been typing "Option Explicit" in, hitting enter, and then pasting the code. But when I just pasted the code next to "Option Explicit" then the macro box came up. I don't know what the issue is with the code. I tried it again without putting the code right next to "Option Explicit" and I got the "Subscript out of range" error again even though I double checked that the correct worksheet names were being used. Any other ideas as to what may be going on? I'm currently on a trail version of Excel 2010 but because the VB script is opening I don't suspect that to be the issue? How does the code know where to find the workbooks? I attempted putting in the full file location but that didn't seem to help either?

Ok, having the books opening definitely helped! However, while doing some proofing to make sure that the data came in correctly I found that I was a getting a lot of incorrect records returned, or at minimum they were going into the wrong place. Suggestions?

I'm very happy with where you put the information. I'm not really sure what is going wrong but for some records the details match and some they do not. I was noticing that even though the fields are being combined based upon the territory ID and year that when the records move over sometimes the territory ID is not matching up at all and sometimes it does. The are blanks in the records, which there should be as not all the records are used but sometimes a 0 or 1 gets entered into fields that never have a 0 or 1. So in the end I don't know what the issue is outside of Excel is connecting records that should not be connected. If you'd like I could send you the two files so you could see them yourself, I don't know if that would be easier than trying to make the code blindly?

I sent you a private message with a public link to the files I am working with using sugarsync. I really appreciate all the help!

I gave it a try but it is still not matching the data correctly. When I combined them it brought in all the records from the "chopped" dataset even though I only need the records that match with the data in the "individual" dataset. It is also still not matching the success records correctly with the territory. It will give two different records to two different birds, both of which were on the same territory in the same year which means they should have the same record copied to them because there is only one nest per territory but there can be 0, 1, or 2 birds recorded for each territory depending on how many individuals are known for a particular year. For example, territory "MATFTCH165" year "1999" ,which is near the top of the file, there are two individuals recorded for the territory, but they both get a different record supplied to them even though they should be identical. Neither of the records supplied is the actual correct record either which is not good either. I feel like I'm being a burden with this and I am wondering whether I should go back to manually copying the records? I feel bad taking up so much of your time but I really appreciate what you've done!

Yep, sorry about any confusion there. I need all of the "individual data" records so I can see who was recorded when and where but for the "chopped" data I only need what matches up to known individuals.

By golly I think that may be it! I need to do some more proofing before I am going to say that it is 100% accurate but I've checked a few records already and it all seems good so far! Thank you very much and if I have any further issues I'll post them here ASAP!

I'm not sure what changed between the most recent edition and the previous one but I did notice that this most recent one had an error...it gave the same individual ID to multiple individuals that originally had unique IDs. Otherwise, the nesting data was correct so I'm not sure why it gave the same individual ID to all individuals that were ever on that territory while maintaining the correct nesting data? I went back to double check the 1st rendition to make sure it had not done the same and it appears to still be ok. I have not thoroughly looked over the combination from the 1st code that worked but I have yet to find an error in the data so far. I plan on checking that thoroughly today and making sure things look accurate and correct. What was supposed to be the difference with this new code because I did not see a difference?

Your test sheet appears to be alright so I'm not sure what I was seeing this morning. I had one territory with a male, female, and hatchling and they were all given the same ID in the rendering but they have separate ones in the original. However, I am not seeing that discrepancy now. Either I found one oddball or something happened differently somehow on my end? The fix you describe is definitely something I will need but I had not noticed that error yet. I will look over this new one that you posted and let you know if there is anything I see that could be better but unless I find something erroneous in the data transfer I think I will be good!

P.S. You mentioned Sebago Lake. I live about 10 miles away on a much smaller body of water known as Forest Lake.

Will do, thanks a LOT!