Hey yall,
Hope all is doing well.
this is my first post and I really hope to learn alot from you guys.
So, recently was assigned to a new report at work and it's taking my whole time to monitor 4 system and then manual extract the data from, covert values, add values, and finally edit name of customers from. The official list of The company.
I noticed all the systems have the option to export all the transactions list to a xls file.
So I'm trying to Create a master workbook with Macros/formulas to automat the following processes:
1) Let a master file extract data from the exported xls files and have the master update everyday when I export the data end of day from the systems
2) If the same customer have several transactions then add them all together Each currency by it self.
3) If currency isn't USD then convert it to USD. We only have the following currencies: (EURO USD CHF GBP CAD) . Then add. All the values together for each customer. (exchange rates are Provided Daily in xls file)
4) To Take the correct customer name from a seperate table that I can add to the Master file and have that table update everytime I get a new Customer from the systems extracted xls.
5a) finally generate a report with three tables: 1) sales and 2) purchase 3) payments. each with the following data
(Cust ID, Cust Name, Transactions total in USD)
5b) only list transactions in report if the value is
>= 100k USD for sales table
>= 500k USD for purchase table
>= 100k USD for payment table
6) finally, all the rest unlisted transactions to be added togegher and be listed under each table as:
total Sales transaction of below 100k
total Purchases transaction of below 500k
total Payments transaction of below 100k
============
============
If yall can just show me a sample file and explain the formula or macro to do the requested tasks.that would be awesome.
Or If you guys sees it as a simple task and decided to be super kind and create the files.
then Please explain to me The macros and formulas in the file so I can understand what's happening.
=============
=============
I really apologize for mumbling alot guys. I'm just Trying to explain the idea well (English is only my 2nd language)
thanks in advance yall for helping me to automat the report and save me from wasting more days on what should. Be done in 10 min end of day
Looking forward to hear from. You guys.
Bookmarks