Tags: TABLE

Create all possible combinations from different ranges without using VBA

{ 122 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 →

Calculate a unique count with conditions in a Pivot Table

{ 12 Comments }

Assume a three column table arranged as follows: Circle, Date of Fault and ID.  Dates in the date range span one week – November 26, 2012 to December 2, 2012.  A particular equipment can be only one specific Region and the same equipment an go faulty multiple times within one week.  Data for one week […]

Read More →

Compute Pro rata growth rate within a Pivot Table

{ 0 Comments }

Assume a four column input data range (refer sheet named “Input” of this workbook) arranged as follows: 1. First tow columns are Group and Type which have text values 2. Third column is a month column with entries such as Dec_11 and Sep_12.  These denote 12 months ended December 2011 and 9 months ended 2012 3. […]

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 →

Force Excel tables to work across workbooks

{ 5 Comments }

Assume a two column database in range B3:C11 of sheet1 of Book1.xlsx.  Headings are in B3:C3.  B4:B11 has names and C4:C11 has numbers.  Save this file on the desktop. Open another workbook (Book2.xlsx) and type any name in cell B6 of sheet1 (the name should be one of the names mentioned in range B4:B11 of sheet1 of Book1.xlsx).  Save this file […]

Read More →

Display text entries in the data area of a pivot table

{ 60 Comments }

Assume a two column database of approximately 20,000 rows (say from A1:B20237).  The first column has vendor names and the second column has Part codes (alphanumeric string).  Headings are in A1:B1, say Vendor in cell A1 and Part_code in cell B1.  One vendor supplies multiple parts and therefore there would be repetitions of vendor names […]

Read More →