Tags: PIVOT TABLES

Converting a matrix data layout to a tabular layout

{49 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 MS Excel 2010 and higher versions

If you are using the Power Query add-in, then a few simple steps (no formulas) can solve this problem.  The result will be dynamic and refreshable (just as in a Pivot Table).  You may refer to Power Query result worksheet.

You may watch a short video of my solution here

Solution for all versions of MS Excel

Please try the following steps:

1. Select A6:F234 on the Source Data worksheet
2. Press Ctrl+G > Special > Blanks > OK.  This process will select all blank cells in range A6:F234
3. With the selection in place, press the = key
4. Press the up arrow key
5. Press Ctrl+Enter.  You will now see all blanks cells filled up the values just above.
6. Select A6:F234, copy the range and paste them as values (Alt+E+S+V)

To denormaise the pivot table, the multiple columns appearing in the row labels area of the pivot table (range A6:F234) will need to be crunched into one column by concatenating.  The result is shown in G7:G35 of "Source data adjusted" sheet.  Likewise, the multiple rows appearing in the columns labels area of the pivot table (range G4:DE5) will need to be crunched into one row by concatenating.  The result is shown in H6:DF6 of "Source data adjusted" sheet.

The process of denormalising is as follows:

1. Select range G6:DF235 of the "Source data adjusted" sheet
2. Press Alt+D+P > Multiple Consolidation ranges > Next
3. Select "I will create the page fields" > Next
4. In the range, box, select G6:DF235 > Add > Next
5. Select New Worksheet > Finish.  A new worksheet will get created with a pivot table in it.
6. From the pivot table field list, uncheck Row and Column.  Only Value should remain checked.  The output should appear as shown in "Sheet2"
7. Double click on 6167 in the pivot table.  Another sheet (sheet3) will open up with data in three columns
8. Insert five columns after column A
9. Select range A2:A23588 and go to Data > Text to columns > Delimited > Next > Other > : > Next > Finish
10. Insert one column after column G
11. Select range G2:G23588 and go to Data > Text to columns > Delimited > Next > Other > : > Next > Finish
12. Give meaningful headings in range A1:I1

To exactly match the data on the "Desired Result" sheet, select range A1:I23588 on "Sheet3" and sort column E (CFA code) in ascending order.

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 solve this problem by using a pivot table but the pivot output was incorrect.  The pivot was returning the farthest/latest date for all status' of a particular Site ID and Customer.  Ideally, it should show only the farthest/latest date and its corresponding status for a particular Site ID and Customer.  Therefore, for a particular Site ID and Customer combination, only one row should show up in the final output.  Therefore, the Pivot Table solution did not work.

You may download this workbook for a better description of the problem and my workaround.

Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem

Solution for MS Excel 2010 and higher versions

If you are using the Power Query add-in, then a few simple steps (no formulas at all) can solve this problem.  The result will be dynamic and refreshable (just as in a Pivot Table)

Solution for all versions of MS Excel

You may refer my Advanced Filter (along with formula) solution in the workbook.

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.