How to merge or combine data from tables with different structures in Excel
QuickRows is an Excel add-in that allows you to consolidate data from tables with different structures automatically.
In some companies it may be necessary to make a report merging data that are received from different places of business. Data consolidation can become a very laborious task if you don’t know the tools that Excel provides you with.
There are several alternatives to consolidate data in Excel in a relatively simple way: with the consolidate tool, with PivotTable or with 3D references in the formulas.
However, the task becomes more tedious (and complicated) when the tables in the diverse spreadsheets have a different structure.
Below you will find a short video on how QuickRows works. If you prefer, you can scroll down to see a written example.
The most outstanding quality of the QuickRows consolidation feature is that it is able to consolidate or join tables that have different structures automatically. But how does it do this?
How to align columns to consolidate data?
All columns sharing the same heading are automatically aligned during the process, i.e. the contents of the cells in each row are distributed “to join pears with pears and apples with apples”.
How to select the sheets to consolidate?
It is actually quite simple. There are three steps to follow:
- Choose the ‘Sheets Consolidation‘ tool;
- Select the folder where the Excel workbooks you want to consolidate are located.
- Once you choose the source folder, a window will pop up (see image below), which contains a list of all the workbooks found, as well as their different sheets and the number of rows and columns in each one;
- Select the required sheets and press ‘Go’. In a few seconds a new sheet appears in the active workbook with the result of the consolidation.
Each of the imported rows includes two additional columns to identify the file and the spreadsheet from which they were imported. This way you can trace all data sources.