Category: DATA APPEND

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 →

Append data from alternate columns of the same table

{ 9 Comments }

On the Data worksheet, data for payments received from different customers is shown invoice wise.  Since payments are received in tranches, they are shown in different columns – column C and D show dates and amounts received in the first tranche respectively and column E and F show dates and amounts received in the second […]

Read More →

Append data from two worksheets with different structures

{ 2 Comments }

Assume a file with two worksheets.  The first worksheet has data in column A:H (8 columns) and the second one has data from B:J (9 columns).  One may want to create a third worksheet with specific columns of data appended from both worksheets – the columns to be shown in the third worksheet are not  […]

Read More →

Compute MODE of all numbers split across multiple worksheets

{ 10 Comments }

Assume numbers are typed in range A1:A2 of multiple worksheets in a workbook.  The task is the compute the MODE of these numbers.  Mode is defined as the value which appears most frequently in a range of cells.  So, if one types 1,3,4,3,5,6 in range A1:A6, then the mode will be 3 – 3 appears maximum number of times in […]

Read More →

Create a Pivot Table from multiple worksheets in different workbooks

{ 67 Comments }

Assume two workbooks named Book1.xls and Book2.xls.  In Book1.xls, there is a sheet named Jan and in the Book2.xls, there is a worksheet named Feb.  In both the worksheets – Jan and Feb, the following exist 1. Same number of columns on both worksheets; and 2. Same order of columns on both worksheets; and 3. Same spellings of […]

Read More →

Create a Pivot Table from multiple worksheets in the same workbook

{ 328 Comments }

Assume a workbook with two worksheets – Jan and Feb.  In both the worksheets, the following exist 1. Same number of columns on both worksheets; and 2. Same order of columns on both worksheets; and 3. Same spellings of headings on both worksheets As you can observe in this workbook, all three aspects mentioned above […]

Read More →