Category: DATA CONSOLIDATION

Create all possible combinations from different ranges without using VBA

{8 Comments}

Assume numbers from 1 to 8 in range B3:B10 and numbers from 9 to 16 in range C3:C10.  The objective is to create all possible combinations in column E from these two ranges without using VBA (macros).  This combined range of all possible combinations is called a Cartesian product.

You may refer to the following steps to create all possible combinations in column E

1. Type a heading in cell B2, say Data Set1.  Format cell B2 to Bold (Ctrl+B).
2. Type a heading in cell C2, say Data Set2.  Format cell C2 to Bold (Ctrl+B).
3. Select range B2:B10, press Ctrl+F3 > New and in the name box, type Data_set1
4. Select range C2:C10, press Ctrl+F3 > New and in the name box, type Data_set2
5. Select range B2:B10 and press Ctrl+T > OK
6. Select range C2:C10 and press Ctrl+T > OK
7. Save the file at your desired location, say on your desktop
8. Select cell E2 and go to Data > From Other Sources > From Microsoft Query > Excel Files > OK
9. In the Select Workbook dialog box, navigate to the folder and select the file which you just saved on the desktop (in step 7 above) and click on OK
10. With Data_set1 selected, click on the > symbol
11. Select Data_set2 and click on the > symbol
12. Click on Next > OK
13. Go to File > Return Data to Microsoft Excel
14. In the Import Data box, select cell E2 in the Existing Worksheet box and click on OK.  Just in case, you do not see the Import Data box, after step 12, press Alt+Tab.
15. You should now see your desired result in range E2:F66.

Furthermore, since you had converted the ranges in column B and column C to Tables individually, if you add additional rows of data to column B and/or column C, just refresh while you are clicked on any cell of the result range.  This will update the table in the result area.

Show granular as well as total figures on the Summary sheet

{0 Comments}

Assume there is data for complaints received and complaints resolved for two years - 2009 and 2010 for some regions regions.  Each Region has its own worksheet.  There are three headings on each sheet - Particulars, 2009 and 2010.  While the Particulars column has the text "Complaints received" and "Complaints resolved", the year columns have number of complaints received and resolved.

The task is to create a Summary sheet which:

1. Show the consolidated figure of complaints received and complaints resolved for both years
2. Show the individual regions which make up the consolidated figure in 1 above.

In this workbook, you may see the data layout and four different ways of resolving the two tasks mentioned above.

You may also refer to the following related posts:

1. Summarise data from multiple worksheets with one condition
2. Summarise data from multiple worksheets with one condition - Part II
3. Summarise data from multiple worksheets
4. Summarise data from multiple worksheets with multiple conditions
5. Summarise data from multiple worksheets with multiple conditions - Part II
6. Summarise data from different cells of multiple worksheets

Consolidate data from a specific worksheet of multiple workbooks to multiple worksheets of one workbook

{8 Comments}

Assume there are multiple files saved in a specific folder.  There can be Excel, Word, PDF, PowerPoint and other files types saved in this folder.  Furthermore, Excel files can have following extensions - .xls,.xlsx.xlsm.  Each Excel file has multiple worksheets with one worksheet being named Sheet1.  While data on sheet1 of all Excel files will start from cell B3 (first header cell), there can be a variable number of rows and columns from cell B3 onwards.

The task is to consolidate data from sheet1 of all Excel files to multiple worksheets of one Excel file.

The Question and Instructions have been clearly explained in the "Question and Instructions" worksheets of this file.

To see the solution work,

1. Save the following zipped folder on our desktop.  Unzip the folder and place Data Files folder (with all its files) on your desktop; and
2. Change the path in cell B2 of worksheet "Query and file listing" of Destination.xlsx file to the path of your folder saved in step 1 above.
3. Click on the blue button on worksheet "Query and file listing" of Destination.xlsx file.

Consolidate data from multiple closed Excel files with different number of columns

{4 Comments}

Assume a folder on the desktop named Test.  Within this folder there is a another folder named Monthly Data.  Within this folder are MS Excel files received every month.  The number of columns in each file is not the same.  Therefore in the January .xlsx file, there would be 100 columns, in the February.xlsx file, there could be 115 columns and so on.  The number of columns in every month's file will always be greater than the number of columns in the previous month's file i.e. If at all the number of columns across files are different, there will only be additional columns, not fewer columns.  Therefore for calendar year 2012, if all files have been received for January to May 2012, the columns in May.xlsx file will always include the columns in other four MS Excel files.  One may very well assume the following:

1. Spelling of headings across all files are the same; and
2. Headings are always in row 1 across all files (from cell A1); and
3. Worksheet tab in which information is present in all files in named "Sheet1"; and
4. In the monthly data folder, there are only those Excel files from where information needs to be consolidated; and
5. The files could be saved in any Excel format i.e. .xls, .xlsx, .xlsm; and
6. The number of rows in each file is variable

In a Summary workbook, one may want to consolidate information from sheet1 of all workbooks lying in the Monthly Data folder.  Given different number of columns in each file, even if one had the patience to copy and paste information, it will not be possible to do so because of different structures.

You may try the following steps to consolidate information:

1. Open the most recent file, say May.xlsx, and copy the headings from row 1 to this new file (paste in cell A1)
2. Press Ctrl+F3 > New.  In the Name box, type FL.  In the Refers to box, type the following:

=FILES("C:\Users\Ashish\Desktop\Test\Monthly data\*.xls")

3. In cell EE2, enter =IFERROR(INDEX(FL,ROW()-1),"") and copy down till blanks start appearing.  This step will list down all file names from the Monthly Data folder
4. Save the attached Excel file on your desktop, press Alt+F8 > Macro1 > Run

You may refer to my solution in the this workbook.

Create a master list of unique account codes from two data sources

{0 Comments}

Assume a two column database in range C4:D11.  In cell C3, type "Cheque No." (without double quotes) and in cell D3, type "As per Bank Account" (without double quotes).  Entries in range C4:C11 are cheque numbers and in range D4:D11 are Dollar values.  Let's refer to this database as "The Bank Dump".

Assume another two column database in range I4:J9.  In cell I3, type "Cheque No." (without double quotes) and in cell D3, type "As per SAP" (without double quotes).  Entries in range I4:I9 are cheque numbers and in range J4:J9 are Dollar values.  Let's refer to this database as "The SAP Dump".

Please note that cheque numbers in range C4:C11 may or may not be present in range I4:I9.  Likewise cheque numbers in range I4:I9  may or may not be present in range C4:C11

One may want to create a reconciliation statement i.e. a three column database showing "Cheque No." in the first column, "As per Bank Account" in the second column and "As per SAP" in the third column.

Herein the first challenge is to create a master list of Account codes from two ranges, C4:C11 and I4:I9 and then the corresponding amounts from the two databases.

Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem

Solution for MS Excel 2010 and higher versions

If you are using the Power Query add-in, then a few simple steps (no formulas at all) can solve this problem.  The result will be dynamic and refreshable (just as in a Pivot Table).

You may refer to my solution in this workbook.

Solution for all versions of MS Excel

One may use using the Data > Consolidation feature.  Please try the following steps:

1. Select cell C15 and go to Data > Consolidate
2. Under Function, choose SUM
3. Under reference, select range C3:D11 and the click on Add
4. Under reference, select range I3:J9 and the click on Add
5. Check the boxes for Top row and Left column
6. Click on OK

The result should be the three column database as desired above.