split output

So i have an excel sheet with 10 tabs or different sheets. I want to output exact same format of 10 sheets but based on a value of another column unique values. So there is a name field in the data. I want to split each file by the number of unique names so that all filters on each sheet are for that particular name only.

Developer

Accepted answers

NMangera

@SouravKayal,

one way to achieve this is by using a formula tool to create a file path, and the output data tool that takes the file path and the field by which you want to split each file

split output.png

split output.yxmd

caltang

This is missing one part still @NMangera but good effort!

@SouravKayal incorporate @NMangera 's post with the following:

Add a formula tool between your file and the output tool:

Then, in your formula tool, write as such:

Use this formula:

"C:\Users\YourUserNameHere\YourFolderHere\ForSouravKayal\Output\"
+
ToString([name])
+
".xlsx|||Sheet1"

That file path is to your desired output location. Please change it according to your PC's desired location and usename.

The extension .xlsx is the extension to output to an .xlsx (Excel) file.

The ToString([name]) is your grouping mechanism. Basically, it is telling Alteryx, for every unique value in that field, make an Excel file for it.

Then, you incorporate @NMangera 's way:

Notice the difference? You change the entire file path, and choose the formularized output path you did above, and remove it from your output fields.

This way, you'll get it like so:

Hope this helps!

Best regards,
Calvin Tang
https://www.linkedin.com/in/calvintangkw/

caltang

@DataNath 's article is pretty good: https://community.alteryx.com/t5/Engine-Works/How-to-Render-to-Multiple-Named-Excel-Sheets/ba-p/1015093

All comments

caltang

Do you have any sample data and sample expected output? That would be most useful.

SouravKayal

So there are 10 sheets and each sheet is different. but will have name column

like 1 sheet-

name id number

A 1 67

A 3 23

B 4 21

So i want one file only for all sheets showing data where Name= A and another file showing all sheets where name = B

NMangera

@SouravKayal,

one way to achieve this is by using a formula tool to create a file path, and the output data tool that takes the file path and the field by which you want to split each file

split output.png

split output.yxmd

caltang

This is missing one part still @NMangera but good effort!

@SouravKayal incorporate @NMangera 's post with the following:

Add a formula tool between your file and the output tool:

Then, in your formula tool, write as such:

Use this formula:

"C:\Users\YourUserNameHere\YourFolderHere\ForSouravKayal\Output\"
+
ToString([name])
+
".xlsx|||Sheet1"

That file path is to your desired output location. Please change it according to your PC's desired location and usename.

The extension .xlsx is the extension to output to an .xlsx (Excel) file.

The ToString([name]) is your grouping mechanism. Basically, it is telling Alteryx, for every unique value in that field, make an Excel file for it.

Then, you incorporate @NMangera 's way:

Notice the difference? You change the entire file path, and choose the formularized output path you did above, and remove it from your output fields.

This way, you'll get it like so:

Hope this helps!

Best regards,
Calvin Tang
https://www.linkedin.com/in/calvintangkw/

NMangera

@caltang,

in supporting others, I'm learning too!

Thank you

caltang

We all are learning - everyday! Thanks for posting too!!

SouravKayal

Super Efforts Guys. WHat if i have 10 sheets within the same file and i want outputs with those 10 sheets filtered

caltang

It goes for all sheets - unless you want to make it unique, then you gotta add some logic to it.

If that helps, kindly mark the above as an accepted solution so that others may benefit + close this thread.

Thanks!

-Cal

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703