Output to multiple excel files AND different headers?

[znooredeen004]

See the attached file.

Originally I had a problem where I wanted to split the text to column by delimiter and needed to line up to record position. This community was able to help me solve this issue so everything lined up correctly and I was able to export John to one excel file then Debra to a separate excel file.

Name:        State                  Income              Subincome           

John            Alabama             4132                  132

John            Arizona               5100                  452   

John            New York            2004                  654

John            Georgia              10400                128

Debra          Alabama             4020                  858

Debra          Arizona               5234                  562   

Debra          Connecticut        2100                  125

Debra          New York           10000                 222

Debra          Georgia              1520                   321

Would it be possible to export to separate excel files for John & Debra but having their name appear as a header in A1 for Example John would be:

John

State                  Income              Subincome           

Alabama             4132                  132

Arizona               5100                  452   

New York            2004                  654

Georgia              10400                128

Debra's excel file would be:

Debra

State                  Income              Subincome   

Alabama             4020                  858

Arizona               5234                  562   

Connecticut        2100                  125

New York           10000                 222

Georgia              1520                   321

[DavidP]

Yes, but you would have to manipulate the data into that format with column headers such as f1, f2, etc. and another column that would be the filename.

You can then use the Take filename from field option in the output data tool and write data without headers to excel.

[znooredeen004]

@DavidP 

Hi David thank you! I just updated the post to include the sample data. Is it possible you can show me on a workflow what that would look like?

[TonyA]

I was working on this between calls using the data from the original posting. The attached gets us most of the way there. I still need to insert the headings, but we have the name and the data pushed out to separate files.

[TonyA · Accepted solution]

Ok, here's the version with the headers.It got pretty involved since I'm trying to ensure that you're not limited to two names. So I isolated the list of names first then appended the field names to make sure I would always have a set of field names for each person's name. I had to play some sorting games to make sure the data was output with the name first, then headers, then data.

[DavidP]

Your new data file (data in slightly different format) obviously changes things, it makes it easier in a way.

Here's my attempt.