+ Reply to Thread
Results 1 to 4 of 4

help moving poorly structured data to new tab

  1. #1
    Registered User
    Join Date
    07-23-2010
    Location
    ithaca ny
    MS-Off Ver
    Excel 2007
    Posts
    38

    help moving poorly structured data to new tab

    i have hundreds of thousands of rows of poorly structured data that i'd like to clean up a in a new tab. I have no idea where to start with cleaning this up a bit in a new tab, though I'm hopeful that VBA can get me there. I'd like some help getting my down the right path with this. An excerpt of my file is attached.

    As i mentioned the data is very poorly laid out so i've color coded the necessary field labels and their matching data points and have included in the second tab a sample of how i'd like the data organized.

    Please help point me in the right direction. And note, I'm completely new to VBA and to programming in general.

    Thank you in advance.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394

    Re: help moving poorly structured data to new tab

    The issue I see is that the data is not consistent. The first 2 sets of data have 2 red cells, the third on has only 1. There needs to be a repeatable method to handle this if you want a macro to fix it.

    There are also products n the market that import data from print spool files, which is what this looks like.

    So is there a clear rule you that can describe the data layout?

    Tis is not that hard, and I would be willing to write some code for you, but you have to be able to explain the rules of the data layout. Example ; there is always 1 yellow cells, and always 2 purple cells, by put the red cells vary in number.
    Last edited by Mallycat; 02-02-2013 at 11:22 PM.

  3. #3
    Registered User
    Join Date
    07-23-2010
    Location
    ithaca ny
    MS-Off Ver
    Excel 2007
    Posts
    38

    Re: help moving poorly structured data to new tab

    thanks for the reply. I'm aware that the inconsistent data is the major issue and I was hoping that someone here had a solution for that. Maybe one solution is to simply merge all yellow red and purple cells into one cell on the new tab. that way it wouldnt matter on how many rows each record takes up, just grab anything from column a and place it in a column a in a single row.

    my main goal is to have each record (separated by the ***) on its own line. if most everything has to be combined into one cell then that's fine. the only field that i need in a unique column is the blue "total full market value". I need to be able to filter on this value.

    thanks for the help

  4. #4
    Registered User
    Join Date
    07-23-2010
    Location
    ithaca ny
    MS-Off Ver
    Excel 2007
    Posts
    38

    Re: help moving poorly structured data to new tab

    Can anyone help with this?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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