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.