Combing different excel sheets into one workbook and producing two tables to include

I run a monthly GL report in order to allow me to understand the total amount paid to each vendor and the corresponding WHT status. To do this currently I ensure all the GL amounts are converted to the correct currency manually and then run a pivot table which gives the supplier and total amount, I then run a vlookup for each supplier from the vendor summary list ("Perm File") which contains each vendor and their WHT status. If I get an error when running the vlookup it will be due to the fact there is a new supplier so I will inquire with regards to its WHT status and update the "Perm" file.

Attached

*Final output file - This is the file I want to be able to click a button and download from Alteryx (I want it to include all three sheets)

Source Data Files

*GL Data

*Perm File

*Feb FX rates

*ILS FX

Problem

*Alteryx Workflow - This is the point I've got to so far - I have managed to get the first table in my excel summary file but not sure how to go about getting the one below it (please refer to summary tab on the final output excel workbook).. Also not entirely sure how I would go about including all the other source data sheets when I download the final workbook from Alteryx.

* If there is a new vendor, it will not be in the vendor summary file and I will need to add it in manually - How do I go about making sure alteryx alerts me that there is a new vendor

If I am doing this on a monthly basis, do I just change the source data to the latest version (the correct month) and click run?

Thanks for all the help!

Workflow Mock Data.yxmd

Workflow

API

Download

Accepted answers

All comments

Yp23

Any help on the above would be much appreciated!

Yp23

Almost there!

So the output I have is the final two select tools, I want to turn them both into tables and have on one sheet on excel.

Before that for the top table, I want to add two columns. I know I can use the formula tool to do such, but not sure what the correct formula would be in excel. Essentially I want the fourth column to have a formula saying if the text in the WHT treatment column is Foreign Vendor or Fully Exempt or Employee, then the WHT rate is 0%, but IF the text says WHT payable then the rate should be 30%.

The 5th column will be easy to add as it will be the third column multiplied by the fourth.

Also is there a way I could add in a total column underneath both tables.

Workflow Mock Data.yxmd

Yp23

@PhilipMannering Thanks for your help in relation to changing field names before using the union tool!

FrederikE

Hey @Yp23,

1. I am not sure I fully understand how the Data for the 2nd table is supposed to come in. Is this:

all you want to achive? If yes - the attached WF provides it.

2. When you want your workflow to alert you when something comes up, the "Message" Tool is a good choise. This would check if there is an unjoined record and then warn you (you can also let the Workflow error instead).

3. A tip about sharing workflows:

You can create a packed workflow including all data (pic) this saves a ".yxzp" and you don't need to include any additional files.

Workflow Mock Data.yxzp

Yp23

@FrederikE

Thanks for your help!

This is the output that I desire

For Table 1, I've got it as the above, however want to add a total row.

For table 2, I've just had a thought that I could create a new data source with two columns (WHT Treatment and WHT Rate) and then join my current table with this new data set in order to get the additional column.
I was wondering whether a formula could do it instead.

Thanks for the tip on number 3! Very helpful.

Is there an easy way to get both these final outputs into a single excel spreadsheet as a table?

Yp23

Anyone able to help with the above!

Just trying to figure out how to get a total column for both and then how to export both tables into a single spreadsheet.
Would be very grateful.

SeanAdams

Hey @Yp23 - not sure if you got an answer for this - but totals are generally just a matter of:

- take a second output from your data into a summarize tool to create the totals you need - keep the same column names as your main table

- make sure you use a formula tool to add in the same columns as your main table - e.g. add in a field called "Supplier Name" wihch has the value "Total"

- then use a union tool to put this row on the bottom of the table - making sure you have the "set a specific sort order" so that the total row comes last.

There are more advanced ways which allow you to do excel formatting using Excel named ranges etc - I know @MarqueeCrew did some articles on this - but the above notes will get you there with a basic table

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703