Automate Transformation

hello,

I have two datasets in Excel, one with daily consumption of resources and other with monthly summary. In daily data I have details of Qty at a individual component level but not its price as per comsumption. Similarly in monthly summary there is comsumed Qty and Price but in a summarized manner based on RID. Both the datasets have one unique column RID. 

Basic requirement is to have Price at a component level.

• My current approach is to append all the daily data in single excel sheet and lookup Price column from different monthly summary tables loaded from different Excel sheets to maintain One-to-Many relationship.
• This price is based on the RID so using lookup does not filter Prices based on month period, Price keeps on repeating on RID level. I manually have to Lookup multiple monthly Price columns from different summary data and then extract only values filtered by period and merge all the price columns.  I use the avg. Price for further calculations and obtain the required output.
• Appending all the monthly summary tables removes the uniqueness as the same RID repeats every month, so for two months there are two duplicate RIDs. This creates Many-to-Many Relationship.

I will append the new data in Excel but want to automate this process and avoid current manual transformations.