Prepare hierarchical and non-numeric pivot tables in Excel

  • Here's the example of hierarchical data in a database:

    CountryRegionCategoryProgramNameDetails_1Details_2Details_3
    USANorthSchoolNameAa1a2a3
    USASouthSchoolNameCc1c2c3
    BrasilEastSchoolNameDd1d2d3
    BrasilEastCollegeNameEe1e2e3
    BrasilWestCollegeNameFf1f2f3


    I've created both class module "CTextTransposer" and module "TestTextTransposer" in Excel.


    After running the TestTextTransposer macro and the results will be shown on Sheet1, starting from cell H10. I would like to seek your kind assistance for enhancing the pivot table with more data fields (highlighted in red below) and field name look like this:


    CategoryUSABrasil
    NorthSouthEastWest
    ProgramNameDetails_1Details_2Details_3ProgramNameDetails_1Details_2Details_3ProgramNameDetails_1Details_2Details_3ProgramNameDetails_1Details_2Details_3
    SchoolNameAa1a2a3Cc1c2c3Dd1d2d3
    CollegeNameEe1e2e3Ff1f2f3


    Attached is my working file for your easy reference. Any comments/suggestions would be much appreciated. Thanks!!