Closed Thread
Results 1 to 2 of 2

HELP! Data clean up...

  1. #1
    Registered User
    Join Date
    10-04-2018
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    58

    HELP! Data clean up...

    I have a report that I pull from a program for work... I need to find the best way to clean up this data and present it but I cannot seem to figure it out... the way the data is created is that someone fills out an online form, then it will add a new line and put in a 1 for each choice made, unless it's a text field. I cannot change this aspect of the way the data is collected or the way the report is presented (the frist tab of my attached example workbook) when I pull it. Therefore, I am trying to manipulate the data into a nicer looking, easier to read table (example in sheet 2 of my attachment).

    I have tried using the delete coloumns option (delete all coloumns with no data) this doesn't work as there seem to be hidden characters that are causing the coloumns to read as not blank.
    I have tried using pivot tables but with all the coloumns its very hard to pull what I need
    I have tried manual...sooo time consuming.

    I am looking for someone who wants to take a look and help with this project... I am familiar with VBA and am hoping there is a way to just click a button and have it sort out where the data is. I have a code that allows me to do this but it pulls all the coloumn headers and the respective responses...but again I have no way to manipulate it.

    I have attached an example work book. This shows how the data is collected/presented in excel and in this example:
    John Smith selected Sydney in the site location drop down on the fillable form so a 1 is added there, Maintenance for Department (again another 1), then filled in Things for project description and typed in Storage Room with and error under work area description, then filled in his name under inspected by along with selecting Training for his position.
    Jane Smith selected Shop in the site location drop down on the fillable form so a 1 is added there, Operations for Department (again another 1), then filled in stuff for project description and typed in shop Room under work area description, then filled in her name under inspected by along with selecting maintenance supervisor for her position. She then added a couple other names for co-inspectors... Which the form allows etc...


    The coloumn count goes to XR (I removed many of them to keep it clean for this example) and so I would love a way to just pull out the relative info instead of just manually sorting!

    I also added in a VBA code that if you run the macro will 'rearrange' the data like a query but.... then what?

    If you are up for a challenge I would REALLY appreciate any help!

    Thank you,
    Chantel
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,998

    Re: HELP! Data clean up...

    Administrative Note:

    Unfortunately, this is a duplicate thread, and you are allowed only ONE thread per issue here.

    Please see Forum Rule #5 about thread duplication.

    I am closing this thread, but you may continue here in the original thread: https://www.excelforum.com/excel-cha...lean-data.html
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

Closed Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Clean Data
    By Excelski in forum Excel General
    Replies: 6
    Last Post: 01-19-2018, 11:10 AM
  2. [SOLVED] Coding Convention Questions #2 (To clean up or not to clean up)
    By mc84excel in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 03-31-2014, 08:18 PM
  3. Help! Data Clean Up!
    By dinozar711 in forum Excel General
    Replies: 3
    Last Post: 03-13-2014, 02:34 AM
  4. [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
  5. Clean Function to Clean Entire Sheet
    By gema in forum Excel General
    Replies: 7
    Last Post: 11-05-2009, 10:07 AM
  6. Clean Up Data
    By ultra_xcyter in forum Excel General
    Replies: 2
    Last Post: 08-11-2006, 03:50 PM
  7. Get out clean XML data
    By Diane in forum Excel General
    Replies: 1
    Last Post: 04-30-2005, 10:06 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