+ Reply to Thread
Results 1 to 14 of 14

Daily Excel Reports, repetitive daily tasks

Hybrid View

  1. #1
    Registered User
    Join Date
    01-23-2014
    Location
    New York City
    MS-Off Ver
    Excel 2010
    Posts
    12

    Question Daily Excel Reports, repetitive daily tasks

    I log onto FedEx.com daily and get reports for two different FedEx accounts. There are things that I do repetitively each time I pull the reports at the end of the day. They are all very simple tasks, like formatting and rearranging the columns, etc. Is there any way I can program Excel to run these tasks automatically?

    Here is what the spreadsheet looks like.

    Tracking number: Service: FedEx Freight Service Guarantees: Your reference: Company:
    777711112222 Priority Overnight N/A 10.13 - 525682, 525694 ABC
    777711113333 FedEx 2Day A.M. N/A 10.13 - 88143, 91757 XYZ
    777711114444 Priority Overnight N/A 10.13 - 525668, 525669 JKL
    777711115555 Priority Overnight N/A 10.13 - 525696 MNO
    777711116666 FedEx 2Day A.M. N/A 10.13 - 525695 GHI
    777711117777 Priority Overnight N/A 10.13 - 525700 DEF
    777711118888 FedEx 2Day A.M. N/A 10.13 - 525703 WXY
    777711119999 FedEx 2Day A.M. N/A 10.13 - 88127 RST
    777711110000 Priority Overnight N/A 10.13 - 88122 QRS
    777711121111 Priority Overnight N/A 10.13 - 525103 BCD
    777711123333 Priority Overnight N/A 10.13 - 525705 TUV
    777711124444 Priority Overnight N/A 10.13 - 525708 VWX
    777711125555 Priority Overnight N/A 10.13 - 525711 FGH
    777711126666 FedEx 2Day A.M. N/A 10.13 - 525712 LMN
    777711127777 FedEx 2Day A.M. N/A 10.13 - 88140 ABC123
    777711128888 Priority Overnight N/A 10.13 - 88139 DEF123
    777711129999 Priority Overnight N/A 10.13 - 88135 GHI123
    777711120001 Priority Overnight N/A 10.13 - 525709 JKL123
    777711130000 Priority Overnight N/A 10.13 - 525666 MNO123

    I'm not sure why the columns aren't aligned here, but you get the picture, I hope.

    I do the following, in this order:
    1. Delete column titled "FedEx Freight Service Guarantees" (I don't import this column, it automatically shows up, and I don't need it.)
    2. Cut and insert "Company" column over to Column B
    3. Sort list by Service
    4. Color fields (30% gray) of tracking numbers for any service that is not Priority Overnight
    5. Sort list again, this time by Company name
    6. Insert a column to the left of Tracking number (after coloring fields, because new cell takes on color of cell to the left)
    7. Insert a column to the left of Service (this column remains blank, as it's my check column when invoice comes in)
    8. In Column A (to the left of the first Tracking number), I enter the number 1, and continue counting to get a count of shipments
    9. I select top row, make all column headings bold and then do some surgery--I remove the colon. ; )
    10. I add a new row to the top of the list and add current day and date and some text: Tuesday, 10.14.2014 - FedEx
    11. I format new row to be BOLD, 18 points and centered between the four columns
    12. At the bottom row, beneath last shipment, I either copy and paste new top row (to copy formatting), or use the format paintbrush, and type in the word "TOTAL:" and put in the number of shipments being sent that evening.

    Whew! I think that's everything! They are all minor tasks, not VERY time consuming, but it would be awesome if I can get all (or most) of these tasks done in one fell swoop. I do this daily.

    Thank you, thank you, thank you!!!

    -Lisa

  2. #2
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2016
    Posts
    2,310

    Re: Daily Excel Reports, repetitive daily tasks

    Hi Lisa;

    have you thought about turning on the macro recorder and walking through your "script"...it needs to be walked through exactly...and then some adjusting "may" be needed for dynamic row count...

    just a thought....
    Ernest

    Please consider adding a * if I helped

    Nothing drives me crazy - I'm always close enough to walk....

  3. #3
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,903

    Re: Daily Excel Reports, repetitive daily tasks

    You could do most of that quite easily by recording a macro and going through the actions one by one.
    With a little bit of tinkering with the code you could quite easily have it all happen at the click of a button.

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Daily Excel Reports, repetitive daily tasks

    Hi,

    In either case below add your additional columns of data to the table of data.

    Then it depends what the ultimate goal is here. If it's a 'pretty' management report with loads of formatting and colours then you'll need either
    1. A predesigned blank layout which contains formulae that can suck the right answers out of your data and conditional formats to colour stuff
    2. A macro to run through your data and build the report on the fly.

    If what you're wanting is the ability to analyse and summarise/total/count data every which way then use a Pivot Table on the original data
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Registered User
    Join Date
    01-23-2014
    Location
    New York City
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Daily Excel Reports, repetitive daily tasks

    Thanks! How do I go about creating a Macro?
    It's a combination of being more pleasing to the eye as well as serving a function of tracking packages and billing. I've never created a macro before. ...But there's a first time for everything!

  6. #6
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,903

    Re: Daily Excel Reports, repetitive daily tasks

    If you have the "Developer" tab visible on your ribbon you can select that and there will be an option for "Record Macro".
    Or, depending on the way you have Excel set up there's a small blue and white square icon with a red dot at the bottom left of the screen. Clicking this will also start the macro recording.
    You then stop the macro recording when you're done in the same manner.

    Without sight of your raw data layout and the expected results, it's difficult to provide you with custom made code for this.

    BSB.

  7. #7
    Registered User
    Join Date
    01-23-2014
    Location
    New York City
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Daily Excel Reports, repetitive daily tasks

    Thanks. I don't see either of those options, though.

  8. #8
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2016
    Posts
    2,310

    Re: Daily Excel Reports, repetitive daily tasks

    follow this procedure

    1) Click on File
    2) Click on Options
    3) Click on Customize Ribbon
    4) On the right hand side you'll see Developer with a check box
    5) Click on the Check Box for Developer

    this will add the developer to the ribbon....

  9. #9
    Registered User
    Join Date
    01-23-2014
    Location
    New York City
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Daily Excel Reports, repetitive daily tasks

    Yes, I found it, thanks! I just have to make it work now. I've done six or seven attempts, but there are still flaws. I need to shade certain cells depending on the data of other cells in that row.

    For example, anything that is NOT Priority Overnight should have the Tracking # shaded.

  10. #10
    Registered User
    Join Date
    01-23-2014
    Location
    New York City
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Daily Excel Reports, repetitive daily tasks

    Addendum: If data in column E (labeled "Service") is any value other than Priority Overnight, then contents in Column B (labeled "Tracking number") should have a 30% grey shaded background.

  11. #11
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2016
    Posts
    2,310

    Re: Daily Excel Reports, repetitive daily tasks

    Hi Lisa;

    I'm guessing the only real way we can help you, is if you upload a sample data set and then we can probably come up with something to really help....

    please remove any company sensitive data....

  12. #12
    Registered User
    Join Date
    01-23-2014
    Location
    New York City
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Daily Excel Reports, repetitive daily tasks

    Thanks, Judgeh59. I did...see original post. I don't know how to upload, so I copied and pasted in the original question. Thanks!

  13. #13
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Daily Excel Reports, repetitive daily tasks

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  14. #14
    Registered User
    Join Date
    01-23-2014
    Location
    New York City
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Daily Excel Reports, repetitive daily tasks

    Okay, I hope I uploaded file samples properly. Thank you!!!
    Attached Files Attached Files

+ 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. Overlapping Times (in daily tasks)
    By blp_ in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-31-2014, 02:24 PM
  2. Repetitive data update (daily)
    By draxz in forum Excel General
    Replies: 4
    Last Post: 07-17-2012, 11:39 AM
  3. Replies: 3
    Last Post: 11-25-2009, 10:21 AM
  4. Turning Daily Reports into Monthly Reports
    By jambezi in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-19-2009, 05:31 PM
  5. [SOLVED] How to import data from daily reports (excel) into excel database
    By Import data from excel to excel in forum Excel General
    Replies: 2
    Last Post: 05-30-2006, 11:25 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