splitting one excel file into many tabs.

[prpatel]

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 solution]

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/t...

Here's an example:

[binuacs]

@prpatel If all your data i one tab with different person name then I think you can create a full path with the formula tool with the person name then update the output tool like below. I attached a sample workflow for your reference

[prpatel · Accepted solution]

@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 solution]

@binuacs,

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 solution]

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).

[prpatel]

@DataNath...I was getting errors, only to realize that the column that I was grouping on cannot have nulls.  When I removed them, it worked well.

Thank you!!!

-Prpatel.

[DataNath]

Hey @prpatel - the way I set it up, you just need to type the file name itself in the text box. There’s no need to add the .xlsx extension :)