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.361980_USD.361989.361989.361989.361989.361989.CO1993.No Country 699_Data Eliminations and allocations699_Data Eliminations and allocations140_Regional corporate costs100_Country corporate costs480_DA GBL Recharge600_Consumer Information core100_Country corporate costs600_Consumer Information core TOTREVEN_Total revenue5,0006,0005,0006,0005,0006,0005,0006,000 EBITXXXX_Earnings Before Interest and Tax20,00021,00020,00021,00020,00021,00020,00021,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 & CountryCompanyBU TOTREVEN_Total revenue EBITXXXX_Earnings Before Interest and Tax1980_GBP.361980699_Data Eliminations and allocations5000200001980_USD.361980699_Data Eliminations and allocations6000210001989.361989140_Regional corporate costs5000200001989.361989100_Country corporate costs6000210001989.361989480_DA GBL Recharge5000200001989.361989600_Consumer Information core6000210001989.CO1989100_Country corporate costs5000200001993.No Country1993600_Consumer Information core600021000 I would then create a sumifs formula and remove duplicates of company to end up with a summary table like this Company New Total RevNew EBIT1980699_Data Eliminations and allocations11000410001989140_Regional corporate costs5000200001989100_Country corporate costs11000410001989480_DA GBL Recharge5000200001989600_Consumer Information core6000210001993600_Consumer Information core600021000 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

Transpose a Hyperion report

ck2024

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

Workflow

Accepted answers

atcodedog05

Hi @ck2024

Here is how you can do it.

Workflow:

Edit: updated solution for using input file.

Hope this helps : )

Transpose.yxmd

input hyperion.xlsx

mst3k

You need to rely on the transpose tool, and ironically I crosstabbed it back into the shape you wanted it in. The summarize tool appears to do what you need in the last step.

Transpose.yxmd

All comments

atcodedog05

Hi @ck2024

Here is how you can do it.

Workflow:

Edit: updated solution for using input file.

Hope this helps : )

Transpose.yxmd

input hyperion.xlsx

mst3k

You need to rely on the transpose tool, and ironically I crosstabbed it back into the shape you wanted it in. The summarize tool appears to do what you need in the last step.

Transpose.yxmd

ck2024

Brilliant, thanks both. The tile tool is a new one for me but understand its worth here, thanks a lot!

atcodedog05

Happy to help : ) @ck2024

Cheers and have a nice day!

mst3k

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703