Forum Discussion
Help pulling data from multiple workbooks
Hello,
I'd be grateful if anyone could help.
I'm working with a large data set, that will be split into 30-50 workbooks for different colleagues to work on different categories. It needs to be different workbooks as further sheets relating to that category will be added at a later date and doing all of this in one becomes too difficult to work with.
The team will be adding their thoughts to a column at the end on each category workbook.
There will be a master workbook containing a copy all of the data and I'm looking to find a way to pull these comments onto this. Not all lines in the categories will have a comment but where there are, I need to bring them all together. Each line has a unique reference that is present in both the master workbook and all of the categories.
IT are still discussing whether we can use power query so for now this isn't an option.
Below is a very basic example for ease. Essentially there needs to be multiple of the first image, one for every individual category. The comments added all need to be brought into the one master workbook as they are added.
Thanks.
3 Replies
This method involves using VLOOKUP to pull the comments from each workbook based on a unique reference. For this example, let’s assume your master workbook is called "Master.xlsx" and your category workbooks are named "Category1.xlsx," "Category2.xlsx," etc. Each workbook has the unique reference in column A and the comments in column B.
If you prefer a more automated way to pull comments without having to create formulas in your master workbook, you can use a VBA macro to consolidate comments.
Assume you have your master workbook with unique references in Column A. Columns B, C, etc., will be used for comments coming from different category workbooks.
