Help creating separate worksheets with unique columns

[missgina]

I need to create multiple files and tabs in excel.  The problem is when I prepare the data using a crosstab, every tab ends up with all columns vs. those populated.  I wish to keep only the columns populated.

Here is some sample data

Dataset	Tabname	Grp	Datavalue
X	1	A	1
X	1	B	2
X	2	B	1
Y	3	C	1
Y	1	B	3

I run a cross tab, grouping by dataset and tabname where the Column Header is GRP and Value is Datavalue

I output the data where I create the filename and sheetname as a combination of the Dataset and Tabname.

The issue is Dataset X, Tab 1 shows columns A, B and C with no data in column C

Dataset X Tab 2 also shows column A, B and C with data only in column B.

How can I limit each tab to have only the columns that have data?

[JosephSerpis · Accepted solution]

Hi @missgina one way you can do what you ask is with a batch macro I have mocked a workflow which uses a batch macro. Let me know what you think.

[missgina]

Thanks for trying to help.  Unfortunately I'm not well versed in macros.  I opened your solution and got a warning that it uses a newer version of Alteryx but I'm on the latest so not sure what the issue is.    I'm getting a ? for your macro with "entry point is invalid" error.  Not sure what I need to change to get it to run.

[danilang · Accepted solution]

Hi @missgina 

Once you've extracted the workflow and the macro from the package, in the directory .\Multiple_Excel open the Excel_BatchMacro.yxmc in Notepad.  Change the version number in the second line from 2019.2 to your version number and save the file.

example

<AlteryxDocument yxmdVer="2019.2"> 

becomes  

<AlteryxDocument yxmdVer="2018.3">

Reopen the main workflow and the macro should be visible

Dan

[missgina]

That worked - thank you...