Create separate files from a Master excel file for each rows

[georgechacko]

Hi All,

I have a Master File abc.xlsx with sample data given below:

Name     Age    Mark1      Mark2   Mark3      Total

a              19      50             60         70          180

b              18      80             40         50          170

c              19      70             60         60          190

I have to create 3 files like given below

1. File Name a.xlsx

Sheet Contents:

Name     a

Age      19

Mark1   50

Mark2   60

Mark3   70

2. File Name b.xlsx

Sheet Contents:

Name     b

Age      18

Mark1   80

Mark2   40

Mark3   50

3. File Name c.xlsx

Sheet Contents:

Name     c

Age      19

Mark1   70

Mark2   60

Mark3   60

Appreciate for any help with an Alteryx workflow.

Thanks in advance

Regards,

George

[kelly_gilbert · Accepted solution]

Hi, @georgechacko - you can accomplish this with the Transpose tool, then by grouping your output in the Output Data tool.

First, use a Formula tool to generate a custom file name for each Name.

Next, use the Transpose tool to transpose the columns into rows, grouping by the output_file name.

Finally, use the Output Data tool to output to Excel, taking the filename from the field you created. This will group all of the "File Name a" rows into one file, all of the "File Name b" rows into another file, etc.

(Note, in the future it would be helpful if you could attach the sample Excel file to your message. You can also attach the Alteryx workflow to show us what you have already tried!)

[georgechacko]

Hi Kelly,

It is absolutely working fine. Thank you so much...

Regards,

George

[georgechacko]

Hi Kelly,

Thanks for your earlier help. I have one more question that how can I format the cells background color as well?

Current output data look like below:

Name        a

Age          19

Mark1      50

Mark2      60

Mark3      70

Column A background color is light Green 
Column A background color is light Blue Required format is

Thanks in advance

Regards,

George

[atcodedog05]

Hi @georgechacko 

Can you provide more info on the background color requirement.

[georgechacko]

Hi,

Thanks for the reply

I have a master file. i have bifurcated the file based on each row and created as separate file. I have to add the formatting also in the data as

A1:A5 - background color - light Blue

B1:B5 - background color - light Green

Thanks in advance,

George A C

[atcodedog05 · Accepted solution]

Hi @georgechacko 

Here is how you can do it. You can do it using reporting tools.

Workflow:

By using groupby in table tool I am converting data into multiple tables based on output file. I am changing color using column rule. And based on output file i am outputting to different files.

Hope this helps : )