demingm13
01-23-2009, 08:21 AM
A little backstory, I have this massive data file that is downloaded from an accounting system that I need to manipulate to get it in a format that I can create some pivot tables from. At this point I am doing it manually by scrubbing any lines that I don't need and keeping the ones I do. I have examples of the before and after product, and I have a general idea of what I want to do, but I don't really know VBA that well and I want to learn (see attached file Example.xls, Uncleaned tab is prior to cleaning, and Cleaned tab is after my manual cleaning effort). I was a comp. sci. minor in school so I understand the basics of programming, I just don't know the proper syntaxes for everything I want to do.
Basically how I am doing this now is I first add a column before column a, and then number the rows from 1 to whatever to give each line a unique identifier. I do a lot of sorting and filtering of the data so I can delete lines I don't need in nice, neat chunks, so its important for me to have these row identifiers because at the end I need the remaining rows to be in the correct order based on how they were in the original file. At the end of the process, basically all that I need is the line that contains the account # (pattern is #####-####-########) in Column B and its description in Column C, all the data lines that have a date in column A, a jrnl # reference in column b (something like ##,###), source reference in column c (GJ or something similar), etc. If you'll notice from my files, there's also a line directly below the lines that have the account # and desc, it has a singular value in Column A and then nothing else. That is the beginning balance for that account and I need that as well. Finally, you will notice that some accounts don't have activity (last two in the file).
So, my basic idea was to loop through all of the rows in my data file, and delete lines that don't fit the basic patterns of the three different types of lines I want to keep. Doesn't seem like it would be terribly hard but like I said I'm relatively new and don't know any of the syntax. If I can cleanse the file to this point, there is some additional manipulation I need to do. I want to add two columns, one for account # and one for account desc, and then I need to do some logic to copy the corresponding acct # and desc from column b and column c and copy them into the two new columns for the beginning balance and detail lines. But I want to do one thing at a time so if I can get to the point that I show in the second file that will be a great start.
I hope I included enough details, if not I apologize and I appreciate any help you can offer me.
Basically how I am doing this now is I first add a column before column a, and then number the rows from 1 to whatever to give each line a unique identifier. I do a lot of sorting and filtering of the data so I can delete lines I don't need in nice, neat chunks, so its important for me to have these row identifiers because at the end I need the remaining rows to be in the correct order based on how they were in the original file. At the end of the process, basically all that I need is the line that contains the account # (pattern is #####-####-########) in Column B and its description in Column C, all the data lines that have a date in column A, a jrnl # reference in column b (something like ##,###), source reference in column c (GJ or something similar), etc. If you'll notice from my files, there's also a line directly below the lines that have the account # and desc, it has a singular value in Column A and then nothing else. That is the beginning balance for that account and I need that as well. Finally, you will notice that some accounts don't have activity (last two in the file).
So, my basic idea was to loop through all of the rows in my data file, and delete lines that don't fit the basic patterns of the three different types of lines I want to keep. Doesn't seem like it would be terribly hard but like I said I'm relatively new and don't know any of the syntax. If I can cleanse the file to this point, there is some additional manipulation I need to do. I want to add two columns, one for account # and one for account desc, and then I need to do some logic to copy the corresponding acct # and desc from column b and column c and copy them into the two new columns for the beginning balance and detail lines. But I want to do one thing at a time so if I can get to the point that I show in the second file that will be a great start.
I hope I included enough details, if not I apologize and I appreciate any help you can offer me.