+ Reply to Thread
Results 1 to 11 of 11

Need Macro to clean up poorly formatted data

  1. #1
    Registered User
    Join Date
    09-12-2013
    Location
    kc
    MS-Off Ver
    Excel 2003
    Posts
    7

    Need Macro to clean up poorly formatted data

    I need some help writing a macro that cleans some ugly data up and makes it presentable. I've attached the sample spreadsheet that I just cannot get to look pretty. We get daily reports like this in excel format and I'm trying to create a macro where I can just copy and paste the daily data onto a sheet, run the macro and it will format the data nicely and allow for a pivot table to be run on it. New people are added every day so the macro would have to account for that. Can anyone take a look at the sample I have provided and point me in the right direction? I am willing to pay for services if needed. Thank you in advance!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Need Macro to clean up poorly formatted data

    Can you provide a sample which shows what you'd like the report to look like after a macro is run?

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Need Macro to clean up poorly formatted data

    I'll let John tackle this, but it could also be helpful to provide an example raw data file before it's imported. Some of the clean up may be minimized with how the data is imported instead of just a vanilla copy\paste.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  4. #4
    Registered User
    Join Date
    09-12-2013
    Location
    kc
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Need Macro to clean up poorly formatted data

    Thank you for your reply! Something like this.... essentially just a sheet that acts as a data source for a pivot table.

    Date Sales Rep Inbound Qty Inbound Time Inbound Avg. Outbound Qty Outbound Time Outbound Avg. All Qty All Time All Avg. NL Qty NL Time NL Avg.
    10/19/2015 Joe (x1441) 111 111 111 111 111 111 111 111 111 111 111 111
    10/19/2015 Dave (x1480) 111 111 111 111 111 111 111 111 111 111 111 111
    10/19/2015 John (x1477) 111 111 111 111 111 111 111 111 111 111 111 111
    10/19/2015 Matt (x1415) 111 111 111 111 111 111 111 111 111 111 111 111

  5. #5
    Registered User
    Join Date
    09-12-2013
    Location
    kc
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Need Macro to clean up poorly formatted data

    Sorry here is a much better depiction of what I am looking for.

    The first line is sales rep name, then drill down to date.

    example.JPG

  6. #6
    Registered User
    Join Date
    09-12-2013
    Location
    kc
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Need Macro to clean up poorly formatted data

    Sorry for the multiple bumps you can delete my prior to replies this should be more helpful.

    I've also attached the actual workbook I'm working out of and the VBA I've used to get this far. Basically I copy a sheet I get daily via email to the "Staging" worksheet, run the macro below and copy paste the relevant data to the "data worksheet. I have to clean the data up a little to make it work, trim the weird time format that comes through on the daily email and change it to minutes. That is what is happening on the data worksheet. Then the PT is pulling from that data. Is this the best I can hope for?

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by alansidman; 10-20-2015 at 03:57 PM. Reason: code tags

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Need Macro to clean up poorly formatted data

    Code Tags Added
    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



    (Because you are new to the forum, I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  8. #8
    Registered User
    Join Date
    09-12-2013
    Location
    kc
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Need Macro to clean up poorly formatted data

    Sorry Alan, I was looking at how to do that, but couldn't figure it out obviously I am a newbie at this. I will comply in the future, already learned something new today thanks!

  9. #9
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Need Macro to clean up poorly formatted data

    Not sure if this could be of help but have you tried to saved you "SampleData.xls" as a txt (tab delimited) file in order to get rid of formats, and then I imported the txt file to excel again as a delimited (tab and space) file?

    Alf

  10. #10
    Registered User
    Join Date
    09-12-2013
    Location
    kc
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Need Macro to clean up poorly formatted data

    Hi Alf,

    the problem isn't really importing the data, although I did just try your suggestion. the problem is translating it into a readable format via a pivot table. I think you will see what I'm talking about if you open my file and see the raw data and what I'm trying to do with it.

  11. #11
    Registered User
    Join Date
    09-12-2013
    Location
    kc
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Need Macro to clean up poorly formatted data

    Anyone? I'm willing to pay if this can be done....

+ 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. Data Clean Up Macro
    By ntate in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 08-27-2014, 11:30 AM
  2. [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
  3. [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
  4. help moving poorly structured data to new tab
    By djc225 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-04-2013, 10:36 AM
  5. 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
  6. [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
  7. Fixing Poorly Formatted Data
    By Spencer in forum Excel General
    Replies: 1
    Last Post: 03-31-2009, 10:14 AM

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