Combining Multiple BLOB created Excel sheets into a single Excel file with multiple tabe

[RyanMonson]

Here's one that has me stumped:

1. Input to the workflow is a 5 sheet Excel file. Each sheet is a "ticket", call them 1,2,3,4,5
2. Each ticket provides data for completing a 3 sheet Excel file. Call these sheets A,B,C.
3. A,B,C are unique and are preformatted with fill colors, cell lines and static data.
4. Each ticket needs to have its own ABC file.
5. I've used the Blob tools to create sheets 1A, 1B, 1C, 2A, 2B, 2C.... 5C (15 total sheets) with the preformatting intact and Alteryx calculated values inserted into the correct cells
6. I need to put the sheets back together (1ABC, 2ABC....5ABC) with their preformatting intact.

Any ideas on 1) how to put the sheets back together in Alteryx, or 2) different Alteryx approach to accomplish step 6?  I've seen Reporting tools used to do this but the workflows get really complicated.

[OllieClarke]

 Hi @RyanMonson 

I've done this before. 

I have a workflow which takes a template, and then populates it with data. 
This is the relevant part of the workflow:

I input the template .xslx as a blob, and then create a fullpath based on a timestamped filename.

My blob output creates a new excel based on the template, with every sheet setup (although with no data).

I then write the data to the formatted empty excel, writing to a named range while preserving formatting. This can be done with a normal output data tool

Make sure you tick option 6, and include a range in your fullpath

<filename>.xlsx|||<sheetname>$<StartOfRange>:<EndOfRange>

This way all you need to do is create your template with its format (for all sheets), and you can dynamically write to it.

So in your case:

- you'd have a template with 1ABC, 2ABC, 3ABC, etc. all setup. (Can do this in excel just by copy pasting on top of each other)

- Read that in using a blob input

- create a filename for the output (probably based on 1-5)

- use that filename to write out the template using a blob output (probably 3 separate excels)

- read in the 1-5 sheets all stacked on top of each other

- split your data stream into A/B/C with 1-5 still unioned

- format the streams into the data structure required for A/B/C

- Create a fullpath field (<Filename>.xlsx|||<Sheetname>%<StartOfRange>:<EndOfRange>) e.g. Output.xlsx|||1ABC$A0:E32 (for the A section of sheet1)
- Have 1 output data tool per range (A/B/C)

- Use 'Preserve formatting on overwrite' and 'Change entire filepath from field' to write the relevant data to the relevant sheet. 

Does that make sense?

Ollie

[Dhrish]

@OllieClarke your solution is apt. But instead of using the union tool we can also make it as a batch macro which reads in each 1-5 sheets one at a time make the respective template files and then output the data in each A/B/C sheets with different output tools