Unpivot data with a formula


Many a times, one may want to convert a column expanding data to a row expanding one (also referred to as “Unpivoting/flattening a dataset”. The easiest way to do so is with the usage of Power Query (referred to as the Query Editor in some versions). The technique to unpivot a dataset with the help […]

Read More →

Restructure the layout of datasets


I’d like to discuss 2 cases of restructuring layouts of datasets: Case 1 Consider the 7 columns dataset below.  For every Doc Number, there are 6 columns – 3 columns for the name of the employee and another 3 columns for the Percent of each employee. Doc Number Emp 1 Emp 1 % Emp 2 […]

Read More →

Converting a matrix data layout to a tabular layout


In this file, the source data sheet is a pasted special pivot table.  The task is to convert this pivot table data layout to the result shown in the “Desired result” sheet.  Effectively, we need to convert a matrix like data layout to a tabular layout.  We need to “denormalise” the pivot table. Solution for […]

Read More →

Display text entries in the data area of a pivot table


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 →