I am new to Alteryx and trying to generate an Excel output file for each entity below with two sheets. The sheet names will correspond to Schedules 1 and 2. I have an output data generated in the workflow as shown below. I have also attached a sample data file to the post. The file path should be c:\Cycle18\CountryNameFrombelow\ The file name should be EntityCodefromBelow.xlsx Sheet names should be Schedulenumberfrombelow i.e. 1 or 2 EntityScheduleAccountAmountCountryPReparerEmailABC11005000BelgiumJoan@abc.comABC120010000BelgiumJoan@abc.comABC1Total 15000BelgiumJoan@abc.comABC240020000BelgiumJoan@abc.comABC2Total 20000BelgiumJoan@abc.comXYZ110030000SwitzerlandAnn@xyz.comXYZ1Total 30000SwitzerlandAnn@xyz.comXYZ230040000SwitzerlandAnn@xyz.comXYZ2Total 100000SwitzerlandAnn@xyz.com Below is a sample of the two sheets: Each sheet should have a header that contains the entity code, Schedule and prepare e-mail. Sheet named 1 EntityABCSchedule1Preparer e-mailJoan@abc.com AccountAmount100500020010000Total 15000 Sheet Named 2 EntityABCSchedule2Preparer e-mailJoan@abc.com AccountAmount3002500040020000Total 45000 Any ideas that will put me in the right track to generate the output Excel files efficiently are greatly appreciated. Purpose SampleData.xlsx

Outputting to multiple Excel files with multiple sheets

I am new to Alteryx and trying to generate an Excel output file for each entity below with two sheets. The sheet names will correspond to Schedules 1 and 2. I have an output data generated in the workflow as shown below. I have also attached a sample data file to the post.

The file path should be c:\Cycle18\CountryNameFrombelow\

The file name should be EntityCodefromBelow.xlsx

Sheet names should be Schedulenumberfrombelow i.e. 1 or 2

Entity	Schedule	Account	Amount	Country	PReparerEmail
ABC	1	100	5000	Belgium	Joan@abc.com
ABC	1	200	10000	Belgium	Joan@abc.com
ABC	1	Total	15000	Belgium	Joan@abc.com
ABC	2	400	20000	Belgium	Joan@abc.com
ABC	2	Total	20000	Belgium	Joan@abc.com
XYZ	1	100	30000	Switzerland	Ann@xyz.com
XYZ	1	Total	30000	Switzerland	Ann@xyz.com
XYZ	2	300	40000	Switzerland	Ann@xyz.com
XYZ	2	Total	100000	Switzerland	Ann@xyz.com

Below is a sample of the two sheets:

Each sheet should have a header that contains the entity code, Schedule and prepare e-mail.

Sheet named 1

Entity	ABC
Schedule	1
Preparer e-mail	Joan@abc.com

Account	Amount
100	5000
200	10000
Total	15000

Sheet Named 2

Entity	ABC
Schedule	2
Preparer e-mail	Joan@abc.com

Account	Amount
300	25000
400	20000
Total	45000

Any ideas that will put me in the right track to generate the output Excel files efficiently are greatly appreciated.

Purpose

SampleData.xlsx

Output

Accepted answers

Thableaus

Hi @Purpose

Ok, I racked my brains this afternoon to set up the solution for you.

I used Run Command Tool to create sub-directory with Country Name (also had to create a formula) and modified the Output Tool to Overwrite Sheet.

(You can see the macro got even bigger)

The Run Command Tool will create a bat file that creates the folder. So this bat file will be in the folder where you're creating your sheets. You can create another bat file to delete it after run, if you want (but that's not the focus now).

Solution appended. I hope it suits you. Have a nice week.

Cheers,

OutputToMultipleTabs.yxzp

All comments

Thableaus

Hi @Purpose

I don't understand where '300' value of account comes from in Sheet 2:

Sheet Named 2

Entity	ABC
Schedule	2
Preparer e-mail	Joan@abc.com

Account	Amount
300	25000
400	20000
Total	45000

Cheers,

Thableaus

@Purpose

Here's a solution that I came up with from what I understood:

This is an Iterative Macro.

- Takes Entity Field, Schedule and E-mail by summarizing

- Gets the first 1 - so it runs one every iteration

- Join Tool is used to get Account and Amount to that Entity/Schedule pair.

1) J side gets these values. I also use Multi-Field formula tool to get field names to enter the Union Tool

2) R side will be what is going to feed Input's next iteration.

- Transpose the first 1 summarized row, so I can get the header information as you asked in your model (row pairs)

- I also use a Text Input with 0 values that I transform to Null so I can insert an empty row in the middle (as your template looks like)

- Union Tool to put everything together. I union by position and configured to the right order

- Create FilePath field with Formula Tool with Entity and Schedule. Folder Browse Interface tool is there so you can choose whatever folder would be the best for you.

- Append FilePath field to dataset

- Dynamic Rename to get Entity and Entity's name as field names, so they come first.

- Output Tool so every iteration is going to be generated a new sheet with a different FilePath (Take File/Table Name Field option is checked).

Here is the package with macro and workflow appended.

Cheers,

OutputToMultipleTabs.yxzp

Purpose

Thanks a lot Thableaus for your response.

Apologies, You are right that there is a mistake in the sample data set output I provided. I am going through your suggestions and will report back.

Purpose

Thableaus,

The workflow and macro worked exceptionally well. Do you know how I can add country as a sub folder?

In other words, the excel output files that belong to Belgium entities should be saved in the Belgium file folder.

Also, I got an error when I reran the workflow, which says that the sheet already exists. This is, I believe, caused by the output files saved from the previous run. Is it possible to overwrite previous files?

Thanks a lot.

Purpose

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703