splitting one excel file into many tabs.

Question

Hi all:

I have an excel spreadhseet that has about 900 tasks for about 20 people.

This is one tab of a spreadsheet.

I've been asked to take the sheet and create a tab for each person.  I'll probably create a separate file from the original.

Is there a way to do this as opposed to writing many filters and formulas?

thanks.

-prpatel

DataNath · Accepted Answer

Hey @prpatel, I'm guessing this sheet has a field in it with each person's name next to the task? If so, I recently build a macro that will allow you to split whatever field you'd like (and the corresponding data) into separate sheets. In your case, you'd select the 'Multiple Sheets' option and nominate the column containing the names as the 'grouping field' - the rest of the configuration is pretty self-explanatory but let me know if you have any issues!

https://community.alteryx.com/t5/Public-Community-Gallery/Output-to-multiple-Excel-files-or-sheets/ta-p/951995

Here's an example:

prpatel · Accepted Answer

@DataNath

I do need some help on how to use this macro.  I've never used a Marco before in Alteryx.

I did download the macro, and opened it in Designer, but I do not think I was supposed to do that.

Any help would be appreciated.

Also, can this macro run on a server?

Thanks.

-Pritesh.

prpatel · Accepted Answer

@binu_acs,

Your solution would work if I had one line per user.  I have 1000 rows of data, with about 20-30 users.  Your approach is not grouping them all.

I think datanath has the solution, but I'm not familiar with how to work with macros.

-prpatel.

DataNath · Accepted Answer

Hey @prpatel no problem :)

At the end of your workflow, where you want to output data, add the macro. You can do this by right-clicking your canvas > insert > macro and then navigating to wherever you have saved it. Once it’s on your workflow, just connect your data stream into that.

It can definitely run on server. You can either upload it to server and reference it wherever it’s stored within the workflows that use it, or you can make sure it’s packaged as an asset within the workflow when you save and publish to gallery (equivalent of zipping).