Transfer Data from multiple tables into report

[IrinaManta]

Hello, 

I am quite new to Alteryx and I already learned a lot searching the discussions in the community, so thank for that. 

Now I have a new Project and don't quite figure out how is the easiest way to achieve the below. 

I have multiple 12 files (one for each month of the year) with multiple sheets all looking like this (all sheets have the same structure):

 I must do 2 things:

 1. I need to transfer the highlighted data into the report below:

- From (date) goes to contract date

- Brand goes to Trademark Name

- Reporting period goes to Date of Transaction

- Royalty Rate goes to Price per Contract 

- the Amounts are calculated fields based on Sales Volume and Total Royalty. 

2. (different output) - I need to append the data in the table so I would have every month populated with the amount from each files for each Brand, like in the example below:

January - input data

February - input data

Result - Goal

Any advise would be much appreciated!

Thank you!

[Amol_Telore]

Hey @IrinaManta 

Can you share file Feb Month Input file and Result (last screenshot) file as well.

Also, does the structure of Input and Output file remains same or it changes month to month?

[IrinaManta]

Hi Amol, 

Much appreciated you are looking into this :)

I have attached the Feb file. The results file (totals), I do not have since that is something I am trying to accomplish through the workflow. What is for sure is that in the results file I wouldn't need the header and the footer just the table filled in with the amounts for each Brand. 

The structure of the information within the sheets would not change, however the number of sheets might (for example in January we have KENT (sheet) but in February we might not have). 

The Output must be a single excel file with sheets for all brands, each sheet having the table filled in with the amounts.

I have also attached the input part of the workflow I have managed to do so far. 

Hope I was clear enough.

[Amol_Telore · Accepted solution]

Hey @IrinaManta 

Hope you are doing well.!

I was able to merge both the reports and generate a combined reports brand wise. Attached is the workflow. Hope this is what you are expecting. 

You will have to put all months file in one single folder with file naming convention as Input*.xlsx. Thou it took some time to come up with this workflow but it helped me to brush up my skills on several areas again.😄

Regarding below report, I am not quite sure if I really understood your requirement completely. It only has one single row. Would you like to elaborate on this little more?

[IrinaManta]

Hi Amol, 

That is fantastic! I thank you so so much for the solution, it works perfectly with all the files as well, truly amazing :)

Regrading the last question, I figured that out out myself after having the consolidated data in one place with your solution. 

Cheers!

[Amol_Telore]

@IrinaManta Glad to help.!