Split rows into different sheets

[beatrizmguerreiro]

Hello, 

I have the following dataset:

Status	Activity	User ID
New	Activity 1	1234
In Progress	Activity 2	5678
In Progress	Activity 3	90123
Completed	Activity 4	90123

Based on the "Status" , is it possible to split the rows into different sheets, in an excel output?

- Keep table structure  

- Rename sheets with Status' name.

Expected end result:

Sheet 1: rename to "New"

Status	Activity	User ID
New	Activity 1	1234

Sheet 2 : rename to "In Progress"

Status	Activity	User ID
In Progress	Activity 2	5678
In Progress	Activity 3	90123

Sheet 3: rename to "Complete"

Status	Activity	User ID
Completed	Activity 4	90123

Thank you in advance.

[AngelosPachis · Accepted solution]

Hi @beatrizmguerreiro ,

Yes that's doable. In your Output Data tool, check the box at the end of the configuration window, and from the dropdown select to change the file/table name based on the field "Status"

You shall get an output with a different sheet for each status

Hope that helps.

Regards,

Angelos

[marcusblackhill · Accepted solution]

Hey @beatrizmguerreiro !

In your output data you need to configurate like that:

Select one place to save the output like excel, with 1 sheet name like example and in the bottom of the tool, check the option "Take file/table" and in dropdown select the field you want to use like guide to split.

Hope that help!

[AngelosPachis]

@beatrizmguerreiro 

as a small note, make sure you change option 3 from create new sheet to either overwrite sheet or overwrite file to prevent your workflow from getting an error

[beatrizmguerreiro]

Hello Angelos, 

I can't make it work. I configured as you said and it's not working 😕 

I didn't mentioned in the first question that I am dealing with a large of volume of rows, so I have to split this large file into multiple files with 100 rows each. Might that be the reason? 

[AngelosPachis · Accepted solution]

Yes @beatrizmguerreiro ,

I really apologise for that, it's my mistake that I didn't test the workflow before sending the screenshot.

If you have set this to overwrite file, do you mind changing that to overwrite sheet or range?

The issue with selecting overwrite a file is that each time alteryx creates a tab, it will bin the old file, hence all the other sheets. So you will end up with a single sheet in your output, the last one in that sheet sequence.

If you change that to overwrite sheet however, alteryx will only replace the corresponding sheet.

I reckon that's the problem you faced?

[beatrizmguerreiro]

Yes, perfect. 

Thank you all very much

[MichaelBouley]

super helpful, thank you!

[Sanjana_HS]

Hi,

In this the status is coming in 1 excel but different tabs what if i want those tabs in different excel sheet

[cwilcoxmtn]

This was a perfect answer for my search.  Thank you!