How to split an excel to multiple excels based on the Labeler number

[Sunithaprdp]

Hi, 

How can we split an excel to multiple excel files based on a criteria.

This is the data in one excel worksheet. I want to split this excel to three excel workbooks based on the labeler. For eg: 00012 data should be in one excel , similarly 00023 and 00043 in different excel workbooks. How can we do that ? Can you please help me?

state	labeler	amount	year
a	00012	100	22022
b	00012	200	22022
c	00012	300	22022
d	00012	400	22022
e	00012	500	22022
f	00023	600	22022
g	00023	700	22022
h	00023	800	22022
i	00023	900	22022
j	00023	1000	22022
k	00023	1100	22022
l	00043	1200	22022
m	00043	1300	22022
n	00043	1400	22022
o	00043	1500	22022

[binuacs]

@Sunithaprdp One way of doing this 

[Sunithaprdp]

Thanks for the solution, however the file consists of huge data more than 1000 lines so how can we modify this with Input tool. When I am trying for that I am not able to customize the Full Path formula. So how can we modify the formula. The actual excel workbook consists of various columns with different headers.

Looking forward to hearing from you.

Thanks and Regards,

Sunitha P

[Sunithaprdp]

the file consists of huge data more than 1000 lines so how can we modify this with Input tool. When I am trying for that I am not able to customize the Full Path formula. So how can we modify the formula. The actual excel workbook consists of various columns with different headers.

Could someone please help me on this ask?

[binuacs]

@Sunithaprdp When you say you are not able to customize the formula tool can you elaborate, please?

in the above example I'm writing all the output files into a temporary location that's why I gave '%temp%' + the field name you want to create the excel files with the name of the fields. Can you update your path in the place of '%temp%'. For example 

please let me know if I'm not making any sense.

[DataNath]

@Sunithaprdp if you don't want to go through the hassle of setting up the formula and output configuration yourself, you could try using the macro I created for exactly this purpose: https://community.alteryx.com/t5/Public-Community-Gallery/Output-to-multiple-Excel-files-or-sheets/t...

All you have to do is download it and then instead of an Output Data tool, at the end of your workflow, right click > insert > macro and then navigate to wherever you saved it.

After that, the configuration is pretty straightforward, you would just pick your [Labeler] field as the one to split on. Of course I'm happy to answer any questions if you get stuck.

[Sunithaprdp]

I am unable to add this macro as I am getting below error. Also wanted to confirm if this macro helps to segregate data based on the criteria as I mentioned?

[Sunithaprdp]

I am not sure if I understood it correctly. Sorry to ask you multiple questions as still I am taking baby steps on this tool.

Do we have to add a new column named Full Path? In my case already my input has got multiple columns of which I have to segregate based on the labeler and then divide this excel into three excel workbooks in order to get three inputs for my ultimate alteryx workflow.

Hope I could convey my concern. Thanks in advance.

[binuacs · Accepted solution]

@Sunithaprdp Yes, you need to add a new column named full path and update the path where you need to save your excel output files.

[DataNath]

@Sunithaprdp yes, the macro will split the data based on the [Labeler] field, as shown: