Output to multiple sheets after removing the previous file

[domlow]

hi there, 

I'm trying to achieve the following with alteryx: A comparison output in excel to return a TRUE/FALSE recon between an automated report vs a manually created report (to validate the accuracy of the automated report).

Manual_1	Manual_1	Blank_1	Auto_1	Auto_2	Blank_2	Recon_1	Recon_2	SheetName
ABC	DEF		ABC	DE		=IF(A2=D2, TRUE,IFERROR(A2-D2,FALSE))	=IF(B2=E2, TRUE,IFERROR(B2-E2,FALSE))	H:\Recon.xlsx|||Page1
123	45		123	40		=IF(A3=D3, TRUE,IFERROR(A3-D3,FALSE))	=IF(B3=E3, TRUE,IFERROR(B3-E3,FALSE))	H:\Recon.xlsx|||Page2

The expected output should be as such:

In Sheet name: Page 1

Manual_1	Manual_1	Blank_1	Auto_1	Auto_2	Blank_2	Recon_1	Recon_2
ABC	DEF		ABC	DE		TRUE	FALSE

In Sheet Name Page 2:

Manual_1	Manual_1	Blank_1	Auto_1	Auto_2	Blank_2	Recon_1	Recon_2
123	45		123	40		TRUE	5

The issue I'm facing is I'm trying to output to the same file and different sheet, I've tried to

Overwrite File (Remove): This only outputs the last sheet (Page 2) in the output as the first sheet is overwritten.

Overwrite Sheet or Range: This outputs both sheets nicely, however, should we introduce new sheet names, the sheet names from previous runs remain in the output.

How can we output each time with only the sheet names from each run? (Ie to delete sheets from previous runs and only output the sheets from the current run?

Tried to refer to:  https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-to-output-multiple-tabs-within-a-s... but they used Overwrite Sheet (Drop) I don't have this function.

Adding the screengrab from the config

[atcodedog05]

Hi @domlow 

Overwrite sheet and overwrite sheet or range is the same feature. You will see overwrite sheet or range is the newer versions.

For your problem what you can do is have generic sheet names that way it always gets overwritten.

Can you provide some sample input and expected output excel files. I can experiment with it.

Hope this helps : )

[NikyN]

Hello @domlow,

For this I always using Reporting Tools.

Please find below attached flow and output.

You need to firstly created table, group by sheet name.

Then with Layout tool specify section breaks between both tables, and name of these sections (sheet names).

Last tool is Render Tool (same as output tool but working only with reporting tools) -- and there you can specify file name.

Let me know if it works!

Best luck!

Niky

[atcodedog05]

This is an awesome idea @NikyN 🙂👍

[NikyN]

@atcodedog05  THANK YOU! 🙂