How do I add fill colors and borders to my Excel data output? Render seems remove it

[alteryxisconfusing]

I read this can be done using the table and render tools and it seems to work, but my issue is that it keeps overwriting my other sheets from my "data output" tool. 

My current flow looks like this:

Data Input = Workbook1.xlsx

Workbook1.xlsx

Sheet1WB1

Block until done > Data Output (Results.xlsx)

Then

Data Input = Workbook2.xlsx

Workbook2.xlsx

Sheet1WB2

Block until done > Data Output (Results.xlsx)

Then

Data Input = Workbook2.xlsx

Workbook2.xlsx

Sheet2WB2

Block until done > Data Output (Results.xlsx)

This results in the following Output

Results.xlsx

Sheet1WB1

Sheet1WB2

Sheet2WB2

a single Excel workbook with multiple sheets (desired result).

What I am trying to add:

I would like to add formatting to "Sheet1WB2", but when I use the table and render tools the entire output file (Results.xlsx) gets overwritten and only shows Sheet1WB2 with the formatting. 

[gautiergodard]

Hi @alteryxisconfusing would you be able to share the workflow or sample inputs/outputs?

The table + render tool combinations should the appropriate way to output formatted cells with fill color etc so I am thinking you may have one of the tools not configured correctly.

Thank you!

[alteryxisconfusing]

Hi, I attached the input files (Workbook1 and Workbook2) and I made a desired version of the Results.xlsx. 

This is what I am trying to achieve.

[gautiergodard]

Hi @alteryxisconfusing 

You could try something like this! You'll just need to enter a new file path for the input tools and in formula tool 24.

Hope this helps.

Please like, and mark this thread as resolved if this provides an answer to your question.

[binuacs]

@alteryxisconfusing one way of doing this

[gautiergodard]

@alteryxisconfusing please accept this solution as complete if it worked for your issue :)

[alteryxisconfusing]

Thank you for putting this together, it's very helpful!

Couple of questions:

1. I changed the formula name of "tab" to something else and noticed it created a sheet1 in the output with a summary of the sheets. Is this because of a setting in the table tool? I think it's the group by that will help eliminate the sheet1. Just want to understand how this logic works.

2. Is it possible to arrange the sheet order? I noticed when I run the tool with more inputs it randomly places the sheets in the output.

3. Is it possible to output to .xlsx with an Excel filter on the headers? That way someone could get the workbook and easily filter the data.

[gautiergodard]

Hey @alteryxisconfusing 

1. I changed the formula name of "tab" to something else and noticed it created a sheet1 in the output with a summary of the sheets. Is this because of a setting in the table tool? I think it's the group by that will help eliminate the sheet1. Just want to understand how this logic works.

The group by functionality in the table tool creates separate tables for each value present in the column you are grouping by. In addition, in the output of the table tools, the group by functionality creates a new column with the value you are grouping by. In the example I'd provided, the group by was used only to get the sheet name value to be attached to the tables, so that it can be referenced in the later layout tool to create the different tabs in the output.

2. Is it possible to arrange the sheet order? I noticed when I run the tool with more inputs it randomly places the sheets in the output.

Sheet order is sorted alphabetically - you can add a 1. ,2.,3.....etc in front of each sheet name and it'll output sequentially in the order you specify. .

3. Is it possible to output to .xlsx with an Excel filter on the headers? That way someone could get the workbook and easily filter the data.

Short answer is no, however users can definitely add a filter on their end once the output has been created. There may be some ways to do what you're asking by using run command scripts but I dont think the effort will be worth the benefit here.

Hope this helps please don't forget to mark the solution as complete if your questions have been answered!!

[alteryxisconfusing]

Ahh so the basis of separating the sheets is based on the "Tab" group aspect in the final render. 

Is it possible to add a crosstab/pivot output with this? I tried doing it, but I keep getting an error in the output saying "we found a problem with some content". This only appears when I try adding the crosstab > table, but I keep the group of Tab in place. 

Otherwise:

- This solution seems to work for what I need, I even added join statements.

- I took your suggestion on sheet order and that worked, thank you! Only downside is I had to put static formulas in for the numbers since I don't know how to have the formula automatically cycle the number inputs.

- Okay, no filter is not a big deal, but I definitely would like to adjust one of the input files to look like a crosstab/pivot table in the output.

[gautiergodard]

Hello @alteryxisconfusing 

Would you be able to share the workflow you are using and a sample of what you are looking to crosstab?

Glad the rest of solutions worked!