+ Reply to Thread
Results 1 to 11 of 11

Data Clean Up Macro

  1. #1
    Registered User
    Join Date
    08-05-2014
    Location
    United States
    MS-Off Ver
    2010
    Posts
    25

    Data Clean Up Macro

    Hi folks,

    So, I have a report that I pull from a back end system. When I import the data into excel it looks quite ugly (see attached).

    I essentially need to get rid of all the formatting and pull back only data.

    This report breaks up teams into tables, and by date and shows the data for each agent that rolls up to their team leader (depicted in a gray cell).

    If possible I would like to be able to paste the data into my "Adherence Data Paste" tab and bring back in my "Adherence" tab the following:

    - The date in column A
    - The Team name (depicted in gray cell) for the agent in column B (if this is to much a pain I have a fairly simple work around, so I'm not to worried about it.)
    -The "Total Time" in column D.
    - The "Duration of Non-Adherence" in column E
    - The "Duration of Non-Adherence Out of Schedule" in column F
    - The "% Adherence in Column G"
    - I would like to create the macro for this so that I may assign it to a form button, simply click and be done.

    If possible I would like it if there is nothing for the team name (my example has two instances of this, one being blank and the other saying <None>
    that the macro simply deletes that table and moves on.

    After the data has been copied/moved from the "Adherence Data Paste" tab I would like to delete everything in the "Adherence Data Paste" tab so that it is blank and ready for the next day's data to be pasted in and run through the macro.

    Lastly, as I will be storing this data for historical purposes, it would be nice if the macro would paste the data at the bottom of "Adherence" tab below any previously pasted data.

    The data as it will be pasted in it's raw form will already be in the "adherence Data Paste" tab as an example.

    I know it's a lot, and I appreciate any help!
    Attached Files Attached Files

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Data Clean Up Macro

    Can you post data in the output sheet so that we can see what entries you are expecting??
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    08-05-2014
    Location
    United States
    MS-Off Ver
    2010
    Posts
    25

    Re: Data Clean Up Macro

    I have attached another example, this time with a few lines of data in the output sheet.

    Obviously I have not entered every line of data that I need, rather I randomly selected lines of data so that way you can get an idea of the expected outcome.

    Thanks!
    Attached Files Attached Files

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Data Clean Up Macro

    Hi,

    Are your posted values correct?

  5. #5
    Registered User
    Join Date
    08-05-2014
    Location
    United States
    MS-Off Ver
    2010
    Posts
    25

    Re: Data Clean Up Macro

    Apologies, everything is correct except for cell g4 in the "Adherence" tab. This should be 100.

  6. #6
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Data Clean Up Macro

    Hi ntate

    Will these ALWAYS be at the top of the Worksheet?
    if there is nothing for the team name (my example has two instances of this, one being blank and the other saying <None>
    that the macro simply deletes that table and moves on
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  7. #7
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Data Clean Up Macro

    Hi NTate,

    Try this - if you're happy with it, remove the apostrophe in the red code:

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    08-05-2014
    Location
    United States
    MS-Off Ver
    2010
    Posts
    25

    Re: Data Clean Up Macro

    Unfortunately no. I have no way of telling if those blank teams will appear or not from day to day.

  9. #9
    Registered User
    Join Date
    08-05-2014
    Location
    United States
    MS-Off Ver
    2010
    Posts
    25

    Re: Data Clean Up Macro

    Oh my gosh xladept, this works perfectly!

    I did have one question, when you run the code and it is pasted into the "Adherence" tab do you think it is possible to have the date and team name run down and copy next to each agent? It would essentially have to run down until it hits the next, first, posted date/name in column A/B change to that date/name and then continue to run down. I'll include an example for reference.

    Again, thank you so much for all of your help!
    Attached Files Attached Files

  10. #10
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Data Clean Up Macro

    Try this:

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    08-05-2014
    Location
    United States
    MS-Off Ver
    2010
    Posts
    25

    Re: Data Clean Up Macro

    Simply amazing, I feel like I've asked to much. (I would post a different thread, but I feel you understand the problem the best by now, haha.)

    When the Macro runs through is there anyway that this formula can be added in the adjacent cells in Column H of the "Adherence Data" tab
    Please Login or Register  to view this content.
    This simply changes the format of the name in column C. I'd rather not drag the formula down every time I post data... If possible. Thanks again, Xladept!
    Last edited by ntate; 08-27-2014 at 07:28 PM.

+ 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. [SOLVED] Macro to clean data
    By D_N_L in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-02-2014, 06:20 PM
  2. [SOLVED] Need help with macro to clean up a table of data
    By FSUMBA97 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-11-2013, 10:37 AM
  3. I need help. Huge data source, I need to clean it up. Find and move data macro.
    By sangreal2007 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-20-2012, 02:26 PM
  4. [SOLVED] Macro to check and clean double data entry??
    By CharlieGough in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-25-2012, 06:26 AM
  5. [SOLVED] VBA Code to optimize and clean data- clean out numerical/ or symbol
    By tracylsr in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-05-2012, 04:21 PM

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