help moving poorly structured data to new tab

**djc225** · 02-02-2013, 09:32 PM

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.

**Mallycat** · 02-02-2013, 11:20 PM

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.

**djc225** · 02-02-2013, 11:34 PM

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