Convert Summary Table to Pivot Form

  • Hi,
    I would need help in converting table which is in table format to a pivot format. Below is the sample data which i have:



    [TABLE="width: 500"]

    [tr]


    [td]

    Product

    [/td]


    [td]

    Currency

    [/td]


    [td]

    Value

    [/td]


    [td][/td]


    [td][/td]


    [td]

    US

    [/td]


    [td]

    UK

    [/td]


    [td]

    Japan

    [/td]


    [td][/td]


    [td][/td]


    [td]

    Ind

    [/td]


    [td]

    Local

    [/td]


    [td]

    MNC

    [/td]


    [/tr]


    [tr]


    [td]

    Product 1

    [/td]


    [td]

    USD

    [/td]


    [td]

    200

    [/td]


    [td][/td]


    [td][/td]


    [td]

    56%

    [/td]


    [td]

    22%

    [/td]


    [td]

    0%

    [/td]


    [td][/td]


    [td][/td]


    [td]

    13%

    [/td]


    [td]

    68%

    [/td]


    [td]

    19%

    [/td]


    [/tr]


    [tr]


    [td]

    Product 2

    [/td]


    [td]

    USD

    [/td]


    [td]

    300

    [/td]


    [td][/td]


    [td][/td]


    [td]

    67%

    [/td]


    [td]

    18%

    [/td]


    [td]

    15%

    [/td]


    [td][/td]


    [td][/td]


    [td]

    0%

    [/td]


    [td]

    85%

    [/td]


    [td]

    15%

    [/td]


    [/tr]


    [tr]


    [td]

    Product 3

    [/td]


    [td]

    GBP

    [/td]


    [td]

    400

    [/td]


    [td][/td]


    [td][/td]


    [td]

    13%

    [/td]


    [td]

    68%

    [/td]


    [td]

    19%

    [/td]


    [td][/td]


    [td][/td]


    [td]

    67%

    [/td]


    [td]

    18%

    [/td]


    [td]

    15%

    [/td]


    [/tr]


    [/TABLE]

    I need in the output in the below format:


    [TABLE="width: 500"]

    [tr]


    [td]

    Product

    [/td]


    [td]

    Currency

    [/td]


    [td]

    Value1

    [/td]


    [td]

    Region

    [/td]


    [td]

    Customer

    [/td]


    [td]

    Value2

    [/td]


    [td]

    Value3

    [/td]


    [/tr]


    [tr]


    [td]

    Product 1

    [/td]


    [td]

    USD

    [/td]


    [td]

    200

    [/td]


    [td]

    US

    [/td]


    [td]

    Ind

    [/td]


    [td]

    56%

    [/td]


    [td]

    13%

    [/td]


    [/tr]


    [tr]


    [td]

    Product 1

    [/td]


    [td]

    USD

    [/td]


    [td]

    200

    [/td]


    [td]

    US

    [/td]


    [td]

    Local

    [/td]


    [td]

    56%

    [/td]


    [td]

    68%

    [/td]


    [/tr]


    [tr]


    [td]

    Product 1

    [/td]


    [td]

    USD

    [/td]


    [td]

    200

    [/td]


    [td]

    US

    [/td]


    [td]

    MNC

    [/td]


    [td]

    56%

    [/td]


    [td]

    19%

    [/td]


    [/tr]


    [tr]


    [td]

    Product 1

    [/td]


    [td]

    USD

    [/td]


    [td]

    200

    [/td]


    [td]

    UK

    [/td]


    [td]

    Ind

    [/td]


    [td]

    22%

    [/td]


    [td]

    13%

    [/td]


    [/tr]


    [tr]


    [td]

    Product 1

    [/td]


    [td]

    USD

    [/td]


    [td]

    200

    [/td]


    [td]

    UK

    [/td]


    [td]

    Local

    [/td]


    [td]

    22%

    [/td]


    [td]

    68%

    [/td]


    [/tr]


    [tr]


    [td]

    Product 1

    [/td]


    [td]

    USD

    [/td]


    [td]

    200

    [/td]


    [td]

    UK

    [/td]


    [td]

    MNC

    [/td]


    [td]

    22%

    [/td]


    [td]

    19%

    [/td]


    [/tr]


    [tr]


    [td]

    Product 1

    [/td]


    [td]

    USD

    [/td]


    [td]

    200

    [/td]


    [td]

    Japan

    [/td]


    [td]

    Ind

    [/td]


    [td]

    0%

    [/td]


    [td]

    13%

    [/td]


    [/tr]


    [tr]


    [td]

    Product 1

    [/td]


    [td]

    USD

    [/td]


    [td]

    200

    [/td]


    [td]

    Japan

    [/td]


    [td]

    Local

    [/td]


    [td]

    0%

    [/td]


    [td]

    68%

    [/td]


    [/tr]


    [tr]


    [td]

    Product 1

    [/td]


    [td]

    USD

    [/td]


    [td]

    200

    [/td]


    [td]

    Japan

    [/td]


    [td]

    MNC

    [/td]


    [td]

    0%

    [/td]


    [td]

    19%

    [/td]


    [/tr]


    [/TABLE]



    If you see for each row of Product 1, i need three rows of country and for three rows of customer type. The only exception is if either of country of customer type is 0% we can ignore those cells. So for example from the above output table we may not need the Japan rows because Japan is given as 0% for Product 1

    Similarly for Product 2, we may not need "Individual" rows because it is given as zero. I do not know how to write vb codes, so tried recording macro, however because of huge volume of data, the macro does not work.

    Also, if it is helpful

    Part A - Consists of Product, Currency and Value
    Part B - US, UK and Japan - basically lists out few countries
    Part C - Customer type - includes Ind, Local, MNC

    Attaching the excel file for your reference



Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!