Creating a dynamic workflow with multiple tabs

[cjefferies]

Hello! - I am fairly new to Alteryx and struggling a bit with a workflow I'm trying to create. I will try to describe the desired output as best as I can below:

The goal is to use a singular input file (with consistent columns each time it's generated, but with different data) and create multiple output files (one for each unique identifier in the first column of the input file). 

These output files should have two tabs: 1. a simple pivot with the unique identifier/account name/product family in the first few columns + remaining columns are end dates with total dollar values aggregated for each end date by product family. 2. the exact same format as the input file, but only for the unique identifier used in the first tab (essentially the input file just filtered for one unique identifier) 

This issue I have been running into is when generating tab #1 - the output file contains all end dates from every unique identifier and there are numerous blank columns in each that are not used. I'm also having trouble combining the two tabs into one file.

Please see below for an example input file as well as desired output. The data to be used is confidential and I have tried my best to change & simplify for the exercise. Thanks so much in advance!

[binuacs]

@cjefferies batch macro works similar to the single workflow , advantage of batch macro is that you can use the single workflow logic to a batch of files, any thing you are able to do with the single workflow should be achieved using batch macro as well, try from your side and let me know if you face any issues

[cjefferies]

@binuacs thanks for your reply

After your advice, I'm able to determine where the data is located by naming the sheet and range, however I'm unable to preserve the formatting from my pre-determined excel file (i.e. column width, bold, etc). 

I updated the formula tool as well as the output data node but still am having no luck.