Create auto sheets and transpose content to excel output

[bobbybalan]

Hello, I have this data. Need to create sheets using BUSINESS_UNITS values and for each sheet show the values in the format shown in Output header. I know how to create the sheets but don't have clue how to transpose the Plan_CODE values into column and METRICS into rows matrix.

Input:
BUSINESS_UNIT PLAN_CODE Metric Count
Bus1 Plan1 M1 15
Bus1 Plan1 M2 0
Bus1 Plan1 M3 15
Bus1 Plan9 M1 1
Bus1 Plan9 M2 0
Bus1 Plan9 M3 1
Bus2 Plan5 M1 2
Bus2 Plan5 M2 0
Bus2 Plan5 M3 2
Bus2 Plan2 M1 2
Bus2 Plan2 M2 0
Bus2 Plan2 M3 2
Bus2 Plan8 M1 5
Bus2 Plan8 M2 0
Bus2 Plan8 M3 5

Output:

Sheet1 = Bus1

             Plan 1         Plan 9

M1        15                1

M2         0                  0

M3         15                1

Similarly, Sheet2 = Bus2 where Plan5, Plan2 and Plan8 will be columns and M1, M2 and M3 will be rows with the counts as values to a specific Plan to Metric.

Thanks much!

[KGT]

Your Value is Count and so transpose everything else. You can then use a Crosstab with [Business_Unit] & [Metric] as Grouping fields and [PLAN_CODE] as the header with [Value] as the value. That will give you your output with [Business Unit] as a column for sheet selection.

[Gaurav_Dhama_ · Accepted solution]

What i have done here is cross tab the data, grouping over business unit and Metric, and using Plan_Code as header and Count as values. After that I am using a batch macro that will just output the data to respective sheets. Why batch macro you ask? So that you don't have null columns in your output.

Attached is the workflow/