Multiple input excel sheets, with formatting to complete and save, with one output

[JoshuaElphee]

I have a massive spreadsheet of data, which through a separate workflow is broken into 60+ workbooks based on one field.  As a next step, I need to take each of these workbooks individually, and provide a total at the bottom (below the last row) for 4 columns.  The totals need to reflect "column XYZ cell 2 through column xyz cell *last cell with data*."  Following this I need to input a row of totals from a separate report under the contract, and finally a row with these two created rows totaled. Each spreadsheet of these 60+ then needs to be saved separately once again.

After this, I need to take that final row of $ and add it into a new workbook for "current totals", with only one row per "Job".  ie. if job 1 shows  $300 total with $100 for area 1 and $200 for area 3, and job 2 shows $100 total with $100 for area 1 and $100 for area 2 (note that totals may not equal area 1 plus area 2 plus xxx) ; the additional workbook would show the job reference in column 1, and total in column 2, with an additional column for each area.  In this example it would show:

*Job      *Total    *Area1   *Area2   *Area3   * Area4

Job 1      $300    $100      $0          $200       $0

Job 2      $100    $100      $100      $0           $0

I am not certain of how to best pursue these actions.  I have attached an example; the final workbook will replicate "Example Prev. Data", but will be based on the new data totals.

I am not finding the proper trainings to explain the process for such an action and appreciate any assistance.  I am playing with wildcard syntax and batch macros, but have not identified a solution as of yet.

[oneillp111]

@JoshuaElphee , it is difficult to determine the specific ask, for the first question about the totals, can you upload an example of an input file and example of a desired output? 

[JoshuaElphee]

Thank you, I am attaching a file here to show an example input, the secondary input, an example individual output, and the final workbook as well.

By sheets:

-Input Example: shows the data as it will need to be pulled as an input.  There may be 60 of these as separate worksheets at a time; the same process should take place for each (Need to have alteryx identify the final row, and add totals below multiple columns

-Input 2 Example: Includes totals from a previous data pull.  I need to search for the same "Job", and pull the totals into the proper cell underneath the current worksheet.  From here I need to total the totals from the current spreadsheet, and totals from the "previous data" out of Input 2 example to create a full workbook for each of the 60 or so inputted files

-Indv. output example: shows the resultant workbook I need to create for each of the 60 workbooks/jobs used as an input - this example is for Job ABC1

-Final Totals: After the above actions are completed, I need a single additional workbook created which shows the "Job", and current totals.  ABC1 is used as an example here, the totals of course match the "Indv. output example" for Job ABC1

I hope this clarifies and greatly appreciate your assistance.  I cannot figure out how to create this workflow.

[jfha97]

@JoshuaElphee , I kind of understood where you were trying to get at, but I think you did not provide us with all information needed.

I tried to put a logic to see if it helps. I did not include input and output tools because I am unsure as to what the end result looks like (what you want in the end).

This workflow covers the formatting logic first. If this is close to what you intend, then we can work with the output.

Let me know, and hope this helps!

Edit: I think I got it now, took me a couple of minutes but this should work now. I have attached the workflow as v2

[oneillp111]

This should get you moving, similar to other solution but this has the percentages, your math seems to be incorrect in the example but this follows the example for the percentage

[JoshuaElphee]

@oneillp111, are you able to clarify on how I can have the resultant workflow automatically run on multiple excel files?

Example:

Input = 60 separate files located at "C:\Users\Desktop\Alteryx input"

Workflow runs once for each separate file/workbook

Output = 60 separate files located at "C:\Users\Desktop\Alteryx output"

[jfha97]

@JoshuaElphee if all the files have the same format & schema, you can run the wildcard C:\Users\Desktop\Alteryx input\*.xlsx, if not you can create a macro to read in each file. This post can help you out: https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Knowledge-Base/The-Ultimate-Input-Data-Flo...

Regarding the output, you can create a column with the following formula:

"C:\Users\Desktop\Alteryx output\"+[Field containing workbook name]+".xlsx|||"+[Field containing sheet name - could be a name/department/business unit etc] or it could be a static sheet name like "Sheet1"

Then you configure the output took like so:

[JoshuaElphee]

@oneillp111 , thank you again for your assistance on this.  Within the rows created under the containers "Get totals from Areas, replace added underscore", "Get Previous Data", and "Get current total", can you help me identify how I can format the results as Currency/accounting in excel?  

If possible, I would like to put a border around each cell as well.  I have been looking into how to do this, however most trainings appear to be based upon columns rather than a new row, and I do not wish to create an error in the logic.

[oneillp111 · Accepted solution]

You can attempt to do this with  Basic table and the render tool as seen in attached, but Alteryx is not great at cell formatting aside Number, string and Date.  So it might look wonky.  A workaround would be an excel macro to do the formatting, but this should get you started just mess around with the formatting in Basic Table tool