These are a set of tools included in “Z-Addin” (Excel -Add-In) which allow you to combine Multiple Workbooks without opening them
1) Merge Multiple File
2) Pull Functions
Download Z-Addin
You can download Z-Addin from the link below. Please provide email address so that if there is any improvement in this Addin, I can share it with you 🙂
There is no need to download again if you have already downloaded “Z-Addin” from another webpage in this website
[email-download download_id=”527″ contact_form_id= “566”]
MERGE MULTIPLE FILES
This is a tool/utility which allows you to merge multiple workbooks into a single sheet in few seconds
Step#1: Put all the Excel Files you wish to combine in One Folder
Step#2: Open a New Excel Workbook/ File >>>Activate the Z-Addin>>>>Press the Merge Multiple Files button and choose the folder.
Step#3: Select the Workbooks and Sheets you wish to combine and Press “Merge”
Functions to Pull “Specific Data” from closed Excel Workbooks
“Connect to Multiple Workbook” are excel tools that can extract and transform data specific to your requirements from closed workbooks. Since these are excel functions, any update of data in closed workbooks is reflected in the masterworkbook
These tools are part of Z-Addin.
TOOLS
The idea behind these tools is that if we can get the path of closed workbooks(our data sources) and also the worksheet names, named ranges, excel tables then we can use PULL functions to retrieve and transform data which we require. There is no need to copy all the source data to destination file.
This involves following steps
- Get a list of files in a folder
- Get a list of “Worksheets” , “Named Ranges” , “Excel Tables” in a workbook
- Choose a Custom Pull Function according to your needs
- “Pull_a_Cell”. Pulls same cell across multiple workbooks
- “Pull_Query”. Uses SQL query to get value at the intersection of a field and a row.
- “Pull_Query_Aggregation”. Uses SQL query to return aggregation of a field/column with or without criteria.
HOW TO USE
Get a list of files in a folder
The animated image below shows how to extract list of files in a folder.
Go to “Z-Tools” tab >>>> Press “Extract files in a folder” >>>> Select a cell where you want the list to be placed >>>>> Select a folder >>>>>>Done
Get list of “Worksheets” , “Named Ranges” , “Excel Tables” in a workbook
Once you any workbook’s path in excel, you can extract worksheet names, named ranges and excel table details using this tool.
Go to “Z-Tools” tab >>>> Press “Get Sheets,Tables,Named Ranges” >>>> Select a cell where you want the list to be placed >>>>> Select Excel file path(s) >>>>>>Done.
The animated image below shows how to extract worksheet names, named ranges and excel table details.
PULL SAME CELL ACROSS MULTIPLE WORKBOOKS
Use the function, Pull_a_cell to pull a specified cell in a closed workbook
This function takes the following arguments
=PULL_a_CELL (WBpath , Sheetname , celladdress)
PRACTICAL USE:
Two years back, we arranged a training course for 100 employees in our company. After training completion, we sent an “EVALUATION FORM” to the participants and requested their feedback. After 75% of the people had submitted their feedback, my boss asked me to consolidate the feed backs received related to the Trainer. This is how PULL_a_CELL function helps me save time. You can use it similar way when you face a similar situation.
The image below shows the feedback form. As you can see, the Feedback related to the Trainer is in “CELL L19”. As per my boss requests, I needed to extract this “VALUE” from 80 excel files.
The animated image below shows how the desired result is achieved in a matter of seconds
“PULL_QUERY” (Use SQL query to get value at the intersection of a field and a row )
This is similar Index Match/ V-Lookup On Closed Workbooks
This function takes the following arguments and can be applied to named ranges, excel tables or specified range(e.g. A1:F15)
=Pull_Query(WBpath,Sheetname,Datarange,REQUIREDFIELD,QUERYFIELD,SQLQUERY)
PRACTICAL USE
Suppose 4 companies A, B, C and D report their financial position each month to you in this template.
And you want to create an interactive dashboard where you can select a month and a financial statement item and get the related values from each company.
The video below shows how this can be achieved
DOWNLOAD
Download the workbooks below to see “PULL_QUERY” function in action. You must activate Z-Addin and change the file paths of “A.xlsx , B.xlsx, C.xlsx, D.xlsx” according to file location in your PC
Illustrative Example Pull_Query function
“PULL_QUERY_AGGREGATION” (Use SQL query to return aggregation of a field/column with or without criteria)
This function takes the following arguments and can be applied to named ranges, excel tables or specified range(e.g. A1:F15)
=Pull_Query_Aggregation(WBpath, Sheetname, Datarange, Aggregation, REQUIREDFIELD, Optional_QUERYFIELDS, Optional_SQLQUERIES, Optional_Operators)
The last three arguments are optional. They represent the SQL query (“WHERE” clause). If you dont want to make SQL query, you can omit the last three arguments i.e.
=Pull_Query_Aggregation(WBpath, Sheetname, Datarange, Aggregation, REQUIREDFIELD)
The examples below illustrate how to use this formula for named ranges, excel tables and specified range
DOWNLOAD
Download the two workbooks below to see “PULL_QUERY_AGGREGATION” function in action. You must activate Z-Addin and change the file paths of “Chapter 14 Samples.xlsx” according to file location in your PC