Hey, I'm struggling with this so wondering if anyone can guide me
I have multiple tables across multiple sheets, and I end up with this in Excel
1980_GBP.36 | 1980_USD.36 | 1989.36 | 1989.36 | 1989.36 | 1989.36 | 1989.CO | 1993.No Country | |
699_Data Eliminations and allocations | 699_Data Eliminations and allocations | 140_Regional corporate costs | 100_Country corporate costs | 480_DA GBL Recharge | 600_Consumer Information core | 100_Country corporate costs | 600_Consumer Information core | |
TOTREVEN_Total revenue | 5,000 | 6,000 | 5,000 | 6,000 | 5,000 | 6,000 | 5,000 | 6,000 |
EBITXXXX_Earnings Before Interest and Tax | 20,000 | 21,000 | 20,000 | 21,000 | 20,000 | 21,000 | 20,000 | 21,000 |
If I were to transpose this in Excel, then I would copy and paste it elsewhere using the transpose function to end up with this (creating Company column based on first 4 characters of Company & Country column) that I have added headers for
Company & Country | Company | BU | TOTREVEN_Total revenue | EBITXXXX_Earnings Before Interest and Tax |
1980_GBP.36 | 1980 | 699_Data Eliminations and allocations | 5000 | 20000 |
1980_USD.36 | 1980 | 699_Data Eliminations and allocations | 6000 | 21000 |
1989.36 | 1989 | 140_Regional corporate costs | 5000 | 20000 |
1989.36 | 1989 | 100_Country corporate costs | 6000 | 21000 |
1989.36 | 1989 | 480_DA GBL Recharge | 5000 | 20000 |
1989.36 | 1989 | 600_Consumer Information core | 6000 | 21000 |
1989.CO | 1989 | 100_Country corporate costs | 5000 | 20000 |
1993.No Country | 1993 | 600_Consumer Information core | 6000 | 21000 |
I would then create a sumifs formula and remove duplicates of company to end up with a summary table like this
Company | New Total Rev | New EBIT | |
1980 | 699_Data Eliminations and allocations | 11000 | 41000 |
1989 | 140_Regional corporate costs | 5000 | 20000 |
1989 | 100_Country corporate costs | 11000 | 41000 |
1989 | 480_DA GBL Recharge | 5000 | 20000 |
1989 | 600_Consumer Information core | 6000 | 21000 |
1993 | 600_Consumer Information core | 6000 | 21000 |
And I just can't replicate that in Alteryx and would love to avoid having to create a macro to achieve the same, so wondered if anyone had any suggestions on how to achieve this please? Thanks a lot
Solved! Go to Solution.
Hi @ck2019
Here is how you can do it.
Workflow:
Edit: updated solution for using input file.
Hope this helps : )
Brilliant, thanks both. The tile tool is a new one for me but understand its worth here, thanks a lot!
Happy to help : ) @ck2019
Cheers and have a nice day!
The "unique value" configuration on the tile tool can act like a record ID tool within subgroups. Like restarting the record ID for each new value of field XYZ