Crosstab Columns to go to 2 different output files based on another column's value

[sophabraham95]

The names and account numbers are dynamic and can change.

We will create separate file based on acctNum as the file name

Can you help me with this issue of how we can dynamically have the columns pertaining to the respective acctNum come up within each file, I have also attached the Alteryx workflow for your reference

So I have the below Data:

acctNum	Name	ID	QTY
1234	Tom	TTTT	0.0008
1234	Tom	CCC	0.8
1234	Tom	EEEE	0.2
1234	John	TTTT	0.0007
1234	John	CCC	0.72
1234	John	EEEE	0.88
4567	Lin	FXAIX	0.0001
4567	Lin	IEF	0.3
4567	Lin	MBB	0.9
4567	Max	FXAIX	0.0001
4567	Max	IEF	0.3
4567	Max	MBB	0.9

when I crosstab this data, I get:

acctNum	ID	John	Lin	Max	Tom	Value
1234	CCC	0.72			0.8	1.52
1234	EEEE	0.88			0.2	1.08
1234	TTTT	0.0007			0.0008	0.0015
4567	FXAIX		0.0001	0.0001		0.0002
4567	IEF		0.3	0.3		0.6
4567	MBB		0.9	0.9		1.8

Now I want one set to go into file named 1234.xlsx as below:

acctNum	ID	John	Tom	Value
1234	CCC	0.72	0.8	1.52
1234	EEEE	0.88	0.2	1.08
1234	TTTT	0.0007	0.0008	0.0015

and the other set to go into file named as 4567.xlsx as below:

acctNum	ID	Lin	Max	Value
4567	FXAIX	0.0001	0.0001	0.0002
4567	IEF	0.3	0.3	0.6
4567	MBB	0.9	0.9	1.8

But currently am getting as below:

file 1: 1234.xlsx

acctNum	ID	John	Lin	Max	Tom	Value
1234	CCC	0.72			0.8	1.52
1234	EEEE	0.88			0.2	1.08
1234	TTTT	0.0007			0.0008	0.0015

file 2: 4567.xlsx

acctNum	ID	John	Lin	Max	Tom	Value
4567	FXAIX		0.0001	0.0001		0.0002
4567	IEF		0.3	0.3		0.6
4567	MBB		0.9	0.9		1.8

[cjaneczko]

If they are Dynamic, how do you know which Accounts should go into which file?

[sophabraham95]

the file name is the account number itself. Its the columns that have to go into the respective files, as per the account number.

ie. John & Tom for 1234 and Lin & Max for 4567

[sophabraham95]

Using iterative macro from this post I was able to achieve this. https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Macro-to-process-ALL-records-4.... 

[binuacs · Accepted solution]

@sophabraham95 a batch macro is the suitable approach for your use-case