I need to merge three spreadsheets that I've downloaded from our database that contain one common column (id #). My first spreadsheet contains id # and columns for contact information (name, email, and phone) and past ticket purchase information (2008, 2009, 2010), my second spreadsheet contains id # and last contact date and last contact note, my third spreadsheet contains 2011 ticket purchases (columns for id #, date purchased, amount purchased, # of tickets).
I've seen a lot of posts on merging common info from multiple spreadsheets onto a single spreadsheet but I need to make sure that ALL information gets compiled together. For instance I could have someone purchase tickets so they would be on spreadsheet #3 but not be on spreadsheet #1. I've been using VLOOKUP with spreadsheet #1 as my primary sheet since it has the most info but if my ticket purchaser isn't on spreadsheet #1 then their info isn't being pulled over. Does anyone know how I can pull the info onto my primary spreadsheet?
Thanks!
Merge three spreadsheets with one common column but sometimes different info
-
-
-
Re: Merge three spreadsheets with one common column but sometimes different info
I assume we are talking about 3 separate Excel files (workbooks) and not 3 separate worksheets in one file.
In either case, the logic would be similar. The trick is to compile a list of the "unique" ID numbers from each of the separate files and then grab the information relative to each ID from each file and paste it all together into another sheet.
This can be done by creating a non-duplicated collection and then looping through each item in the collection to grab the data that is relevant to that ID on each of the files.
The attached example probably makes this easier to understand. The code is in the Merge1 file and the other 2 files try to emulate what you've said about the the data in the other files.
For demo purposes, the data was created just to confirm it can be merged successfully. There is also an assumption that the data is on the first sheet (starting at row 1, column 1) and that the ID# is in column A on each of the workbooks. Row 1 on each workbook is assumed to be a "header" row.
Unzip all the files into the same folder. Then open the Merge1 file and click the button to create the merged data. The merged data ends up on another sheet in the Merge1 file. The Merge2 and Merge3 files do not have to be open in Excel for this to work.
-
Re: Merge three spreadsheets with one common column but sometimes different info
Of course, the caveat with my code example is that IDs are "unique". However, the Find method that is used in the code would probably be better changed to ensure that the correct ID is always found. At the moment the code looks something like:
CodeSet rFound = .Columns(1).Find(What:=Item, After:=.Cells(1, 1), LookIn:= _ xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False)
The LookAt parameter is currently set to xlPart. However, this means that if you have an ID of 12 and and ID of 120 it is possible that the code will find the ID of 120 when looking for an ID of 12 (depending on the order of the IDs).
So it is probably best to change all the references to LookAt:=xlWhole instead. -
Re: Merge three spreadsheets with one common column but sometimes different info
error while merging three files With wbMerge1.Sheets(MergeSht)
-
Re: Merge three spreadsheets with one common column but sometimes different info
If you expect to get an answer to your problem, I'd suggest you start a new thread, give it an accurate title, state your problem clearly and concisely and include a description of the error along with code samples and possibly a copy of your workbook.
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!