Tags: PIVOT TABLES

Converting a matrix data layout to a tabular layout

{ 74 Comments }

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 →

Extract farthest/latest date based on multiple conditions

{ 0 Comments }

Assume a three column database showing Site ID, Customer, Status and Requested Date.  On the same site ID, the same customer may have different status on different dates.   In such a scenario, one may want to know the farthest/latest requested date and its corresponding status for all unique combinations of Site ID and Customer. I initially attempted to […]

Read More →

List down most frequently appearing names in descending order of frequency

{ 0 Comments }

Given a list of names in a worksheet range, one may want to pull out, the most frequently appearing names in descending order of frequency, to another range.  Furthermore, one may want to provide an additional date criteria as well. You may refer to my solution in this workbook.

Read More →