+ Reply to Thread
Results 1 to 5 of 5

Automate a daily report to extract data from several xls files

  1. #1
    Registered User
    Join Date
    07-01-2019
    Location
    Charlotte
    MS-Off Ver
    Mac Microsoft office
    Posts
    3

    Post 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.
    Attached Files Attached Files
    Last edited by Dalramdhan; 07-01-2019 at 05:54 PM. Reason: uploaded WORKBOOK example

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2412 (Windows 11 23H2 64-bit)
    Posts
    88,180

    Re: Automate a daily report to extract data from several xls files

    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.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    07-01-2019
    Location
    Charlotte
    MS-Off Ver
    Mac Microsoft office
    Posts
    3
    Hello, thanks for clarification. Im working on the sample
    Last edited by Dalramdhan; 07-01-2019 at 02:23 PM.

  4. #4
    Registered User
    Join Date
    07-01-2019
    Location
    Charlotte
    MS-Off Ver
    Mac Microsoft office
    Posts
    3

    Re: Automate a daily report to extract data from several xls files

    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
    Last edited by Dalramdhan; 07-01-2019 at 05:19 PM.

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    18,439

    Re: Automate a daily report to extract data from several xls files

    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.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 10-18-2017, 02:12 PM
  2. Replies: 4
    Last Post: 02-01-2014, 10:37 PM
  3. Replies: 15
    Last Post: 11-11-2013, 08:21 PM
  4. Replies: 2
    Last Post: 09-28-2013, 02:40 AM
  5. How to use VBA to extract excel files from daily emails from outlook?
    By eajustin15 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-18-2013, 02:38 PM
  6. Replies: 2
    Last Post: 11-19-2010, 07:49 AM
  7. [SOLVED] How can I extract automat. the email from a list of hiperlinks?
    By Rafa in forum Excel General
    Replies: 0
    Last Post: 10-27-2005, 05:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1