Need help to Arrange and Present data in tubular format using VBA

I've tried to search for a similar code on the internet but maybe i'm not using the correct combination of words.

I have a set of data that needs to be presented in a tabular format. Excel does have the option of pivot however even after doing a pivot theres loads of formatting and merge cells done on the data from pivot to make it look presentable. I have a feeling that the output can be achievable using VBA and hence need some expertise on this matter.

I've attached a sample workbook (I use Excel 2013). Sheet 1 contains Raw Data and Sheet 2 contains the output where i've included both the Pivot table as well as the formatted table which is the actual end output.

Any help on this would be highly appreciated.

Please Login or Register  to view this content.

Simply Wow. Jindon your code perfectly replicates my output. I read through the code however couldn't apprehend how it works so flawlessly.. probably because the code is at an advanced level and I'm just a beginner at VBA. If its not too much trouble would it be possible to briefly explain what each section of the code does as it would help me to do some self learning. Thanks again.

Brief explanation.

1) Store all the information in objects, SortedList, ArrayList, Dictionary.
2) Prepare output array to transform and place individual data in appropriate position in an array.
3) Cosmetic the format in the second sub.

Google about the objects for details.