Category: DATA CONSOLIDATION

Merge and append data from two worksheets

{ 6 Comments }

Visualise a workbook with two worksheets. The tab name of the first worksheet is “My Table” and of the other worksheet is “My colleagues Table”. Each worksheet has 4 columns – First Name, Last Name, Pin code and Mobile Number. In “My Table”, the first, second and third columns are filled up and in “My […]

Read More →

Create all possible combinations from different ranges without using VBA

{ 124 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 […]

Read More →

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 […]

Read More →

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 […]

Read More →

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

{ 6 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 […]

Read More →

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

{ 2 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 […]

Read More →