Automate a daily report to extract data from several xls files

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.

Welcome to the forum!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Hello, thanks for clarification. Im working on the sample

hey guys i have just uploaded the samples to the original post. hope that can help explain things

ALSO ADDED MASTER FILE V1.1 which shows exactly all needed to be done for the reports

I can't find from where the information comes. For example on the Customers Names sheet of the Master Workbook v1.01 it states "this report will have data improted from customers name workbook: new Customers names". The closest match in the Workbook Samples file is Correct Customers Name Spellings which has no new Customers names column.
That said if you have a newer version of Excel, corresponding to PC versions 2016, 2019 or the subscription version (365) then you might want to consider using Get & Transform (Power Query) for your task. Linked below is a video from Highline College that deals with importing information from multiple Excel files.
https://www.youtube.com/watch?v=_csX8sCzJd0
If you need further help I suggest narrowing the scope of the request to automating one of the five processes listed in post #1. Once that process is automated and the thread marked as 'Solved', then a new thread may be opened to work on the next.
Let us know if you have any questions.