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

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

Sample.xlsx

Sam.xlsx

Developer

Developer Tools

Batch Macro

Accepted answers

ChrisTX

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/ta-p/20480

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-macro-and-setting-up-an/m-p/1039900#M257478

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-macro-and-setting-up-an/m-p/1040648#M257628

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

Chris

All comments

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