Transferring Data from Multiple Workbooks to a Master Workbook

Suppose I have multiple workbooks, each of which contain item IDs. One of these workbooks is a master list containing all IDs, and every other workbook contains a small piece of the master list. Additional information is added, in each small workbook, beside each item ID and the workbook is marked as complete. What I would like to do is have Excel check these smaller workbooks against the master list / master workbook. In particular, I would like Excel to check small workbook item IDs one by one and find the same ID in the master workbook. When a match is found I would like Excel to paste the new information gathered in the smaller workbooks to the master workbook.

Unfortunately, there is a catch: Each smaller workbook has one ID reviewer, but the master workbook sometimes pairs these reviewers together to help ensure that all the necessary information has been included (two pairs of eyes are better than one). This means that the new information added by reviewers 1 and 2 is mashed together so that we have columns D1 E2 F1 G2 H1 I2 and so on, where 1 stands for person 1 and 2 for person 2. Note that D1 and E2 contain the same kind of information, just filled in by a different person. As such, for Excel to properly paste the information from the small workbooks it needs to not only check IDs, but once it has confirmed an ID match it needs to check whether the person's name in the small workbook (which is always 5 columns over including the 1st ID-bearing column) is 3 columns over or 4 columns over in the master workbook. If it is 3 columns over it needs to paste into D,F,H and so on, avoiding E,G, and I. The opposite is true if it is 4 columns over. It may be worth noting that it is possible for the 4th column over to be blank if only one person has been assigned to checking that particular item (the system is far from perfect).

Would it be possible to design a macro that works by highlighting a range of IDs in the master workbook and automatically checking all small workbooks in a given file directory for IDs and names, and filling in the relevant information as described above? Please let me know if anything is unclear, as I realize this is a very specific question.

Would something like this be a good start?

Please Login or Register  to view this content.

Hello Stopwatch,

Welcome to the Forum!

Your explanation is excellent. When coding a custom solution, it is faster and more accurate to work with the data you are working with. If you post a copy of the master and one or two of the "smaller" workbooks, it would be appreciated.

If you files are quite large, you could zip them into a single archive. That will save space and keep all the files together.

How To Post Your Workbook

1. At the bottom right of the Reply window, Click the button Go Advanced
2. At the top of the Your Message Window, in the first row of icons, Click the Paperclip icon.
3. Click the Add Files button at the top right in the dialog box. This displays the File Manager dialog.
4. Click the Select Files button at the bottom middle of the dialog.
5. In the new window Find the file you want to upload, select it, and Click Open.
6. You will now be back in the File Manager dialog. Click the bottom Middle button Upload File.
7. Wait until the file has completely uploaded before you exit the File Manager dialog.

Hello stopwatch,

If you don't have access to the workbooks, I can wait until you do. Without seeing the data and the layout, I will be continuously guessing about what you need or want to do. As a moderator, I can not afford to spend my time that way.

Does anyone have any suggestions?