Hello,
I want to develop a workflow which generates the result like pivot but it should be in vertical order and merged cell.
Example: we have to filter each dept name(dynamically), sum the bonus and count of annual salary , now the result must be in row order not like column order,
cells must be merged based on business unit of respective department. this report is generated on monthly basis so i have data like below in a work book with different sheetname based on department and sum and count should be performed business unit of respective department, sum.bonus should come one by one with sub total of each category. above is just an example i have 1million records uniq depart name - 30 values, business units also N numbers and calculation column like bonus, salary similarly i have 14 columns to perform average, count and sum but each category must have subtotal of each dept, please feel free to write if you have any questions
Master Data sample:
Employee ID | Department | Business Unit | Annual Salary | Bonus % |
E02004 | IT | Research & Development | $83,576 | 0% |
E02005 | IT | Corporate | $98,062 | 0% |
E02007 | IT | Manufacturing | $66,227 | 0% |
E02010 | IT | Manufacturing | $97,630 | 0% |
E02012 | IT | Manufacturing | $40,499 | 0% |
E02014 | IT | Manufacturing | $150,558 | 23% |
E02017 | IT | Corporate | $64,208 | 0% |
E02020 | IT | Specialty Products | $95,729 | 0% |
E02023 | IT | Research & Development | $83,323 | 0% |
E02031 | IT | Specialty Products | $72,860 | 0% |
E02035 | IT | Corporate | $97,509 | 0% |
E02048 | IT | Specialty Products | $54,700 | 0% |
E02050 | Mech | Research & Development | $65,109 | 0% |
E02052 | Mech | Corporate | $72,388 | 0% |
E02054 | Mech | Specialty Products | $74,004 | 0% |
E02057 | Mech | Manufacturing | $76,659 | 5% |
E02073 | Mech | Research & Development | $81,083 | 0% |
E02077 | Mech | Specialty Products | $83,070 | 0% |
E02079 | Mech | Corporate | $70,923 | 0% |
E02085 | Mech | Manufacturing | $99,937 | 0% |
E02093 | Mech | Manufacturing | $72,637 | 0% |
E02095 | Mech | Manufacturing | $68,592 | 8% |
E02097 | Mech | Research & Development | $65,073 | 0% |
E02107 | Mech | Specialty Products | $55,039 | 0% |
E02115 | Mech | Research & Development | $41,946 | 0% |
E02116 | Mech | Corporate | $79,388 | 0% |
E02117 | Mech | Corporate | $83,854 | 0% |
E02118 | Mech | Specialty Products | $126,550 | 15% |
E02122 | Mech | Research & Development | $79,975 | 0% |
E02124 | Mech | Corporate | $239,395 | 33% |
Excel Pivot Output:
but i want output to be created like below in sheet name IT :
Pivot generated right side data | Business Unit (Dept based) | Value |
Count of Annual Salary | Total | 12 |
Corporate | 3 | |
Manufacturing | 4 | |
Research & Development | 2 | |
Specialty Products | 3 | |
Sum of Bonus % | Total | 0.23 |
Corporate | 0 | |
Manufacturing | 0.23 | |
Research & Development | 0 | |
Specialty Products | 0 |
and Second output to write in Mech sheet name:
Pivot generated right side data | Business Unit (Dept based) | Value |
Count of Annual Salary | Total | 18 |
Corporate | 5 | |
Manufacturing | 4 | |
Research & Development | 5 | |
Specialty Products | 4 | |
Sum of Bonus % | Total | 0.61 |
Corporate | 0.33 | |
Manufacturing | 0.13 | |
Research & Development | 0 | |
Specialty Products | 0.15 |
attaching image since table format got changed
@AbhilashR @grossal Please assist asap