How to output to mutiple sheets in excel by column headers. is this possible?

[Kennya12]

Hi everyone,

I've been trying to come up with a way to output my workflow to an excel workbook using the column headers to make different tabs. For example per the snip attached, I would like my output to have a different tab for each name and stratifies the info to that tab. So I would want Dan for example to have his own tab and have both the 51 column and the 29 column to be in his tab.

 

[darryl5280]

Your connection to Excel looks like this “Results.xlsx|||Sheet1“ in the “Output” Tool, so in a “Formula” tool create the full path and filename with this at the end “|||” + [Column_Name].

And at the bottom of your “Output” tool Configuration window pick the following options, where “FileName” is the field you created in the previous “Formula” tool.

[Claje]

Limiting what columns are output to each individual sheet in a file is an interesting problem.  I've attached a workflow and a batch macro that does this using Dynamic Select.

I tried to write up a brief explanation of how I did this in the tool notes, but if you have any questions, let me know!

[No-Sass]

This was a good one. I recreated your file and used the Dynamic Rename to pull the names into column headers. If a name appeared more than once there would be a number auto assigned to the column header for each instance where a name appeared.(You have them appearing twice so the second set of names have a '2' at the end - 'Zack2'). Use the Transpose tool to pull all the column headers into one column  "Name" and associated values into a second column "Values". Use the RegEx Tool to remove all instances of numbers from the end of the names. Then export out to an Excel file and use the "Take File/Table Name From Field" selection from configuration on the bottom of the options on the Output Tool. Each name will get its own tab and the data will be consolidated accordingly. 

[avisingh96]

Hi, 

Thanks for this solution. Is there an option if I can leave first 2 columns and do the exact same thing for rest of the columns. 

Thanks in advance