How to dynamically select columns and output to different sheets in same excel file

[sridharthemass]

 Data 

rule_id	serial_no	city	country	region	entity
1	45	melbourne	australia	AUS	A1
1	46	sydney	australia	AUS	A1
1	78	adelaide	australia	AUS	A1
1	33	new york	USA	US	A2
2	889	hong kong	China	CH	B1
2	96	melbourne	australia	AUS	B1
2	12	sydney	australia	AUS	C1

Metadata

rule_id	field name
1	serial_no
1	city
1	country
2	serial_no
2	region
2	city
2	entity
2	country

In the Ouput will contain an single excel file with different sheet names based on ruleid and its associated columns selected dynamically for the above input there will be two sheets with names '1' and '2'

Note the metadata and Data tabs columns will change dynamically

Can anyone help me in solving this using batch macro or any other feasible efficient solution

[DawnDuong · Accepted solution]

I'm using Alteryx 2021.2.1, probably a version control issue. I attached here the macro and the standard workflows.

All saved as relative paths, so should work.

You need to update the inputs' connection to the macro / or insert the macro to the workflows... either way should work.

Cheers,
Dawn

[sridharthemass]

Thanks a lot 🙂

[DawnDuong]

Hope it works for you.

The tools in the macro should work in alteryx 2019.xx as well... There is a thread somewhere in the community that show you can "manually edit" the version of the workflows via editing the text pad.

Cheers.

Dawn.