Split Excel worksheet with multiple tabs into multiple worksheets based on a column

[Katewww]

Can some one help me to resolve this issue.

I have a excel data where i have Name filter in all the 3 tabs and if i take an example of Sam all the 3 tabs has to be filtered for Sam and out put should be split into same number of tabs.

The only problem is I don't have the same layout data or columns in all the sheets. but the Name column is present in all the sheets. Still does it work?

I am attaching the sample file, as well as how the out put should look like for the name "Sam"

The real data has multiple names 

[ChrisTX]

Are you trying to create an Alteryx app, and allow a user to select a value to filter on, then output the same 3 sheets?

Or are you trying to use only Excel?

[Katewww]

Hi Chris,

Thanks for replying so quickly, no I’m not trying to create an Alteryx app, just trying to create a workflow where the resultant excel sheets will saved in one folder 

[ChrisTX · Accepted solution]

For input data:

Since your sheets have different formats, you will need to use a Batch Macro to read them all in.  

See this article:

Community > Designer > Designer Knowledge > The Ultimate Input Data Flowchart
https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/The-Ultimate-Input-Data-Flowchart/t...

For data processing:

To Filter the data on all sheets, you would need either 1) an Interface tool (which may lead to creation of an app, or you can run a Workflow in Test mode to enable the Interface tools), or 2) you could hard-code a Filter tool

For output data:

Since your sheets have different formats, you will need to use a Batch Macro.

Check out these posts:

batch macro and sample workflow: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Clear-the-output-file-before-running-a...

detailed explanation how to structure multiple calls to the same macro: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Clear-the-output-file-before-running-a...

and if you'd care to add a Like to my Idea:  Enhance options for Excel output - Alteryx Community

Chris