Import a CSV File, Edit / Reformat It via VBA, and Output a Usable Data Table

[Jeff Meyers]

Scenario:<o></o>
I obtain a CSV file from the accounting system summarizing budget and expense amounts by project grant (i.e. departments) by G/L account. The problem is that it needs to be “cleaned-up” before I can utilize it as it is more like a report than a data table.<o></o>
<o></o>
The linked Excel file has 2 worksheets:<o></o>
· The first worksheet is the CSV import<o></o>
· The second worksheet has been manually processed to my desired data table format<o></o>
<o>
https://www.box.com/s/273a06a2c11c69eb6dd7 </o>

My Goal:<o></o>
To go from the CSV import to the Processed Data Table automatically / programmatically<o></o>
<o></o>
Notes:<o></o>
· The linked file is just a sample that contains the summary for three project grants. There could be up to 40 or so in another CSV import - the number is not static.<o></o>
· The project grant numbers / identifiers arealways in this format: OA#####FY## (ex. OA21680FY12)<o></o>
· Each project grant may have as few as one (1) G/L account up to sixty (60) or so G/L accounts<o></o>
· The G/L account numbers are always in this format: ###### (six digits) (ex. 510040)<o></o>
· The column headings (Row 5 on the CSV Import worksheet) are not aligned properly<o></o>
· The only data that I need from this CSV Import worksheet are in Columns B, C, & D, along with the associated project grant number / identifier. In addition, I only need this data from the rows that contain a G/L account number in Column B. <o></o>
<o></o>
Editing / Sorting/ Processing:<o></o>
I manually edited the data from the first worksheet (CSV Import) to create the second worksheet (Processed) as follows:<o></o>
· I copied the project grant number to Column A. The project grant number precedes the associated G/L accounts in Column B (i.e.“OA21680FY12 - 2 MIPPA AAA” in Cell B7). The project grant number is also at the end of each group of associated G/L accounts in Column A (i.e. “TTLS for OA21680FY12 - 2 MIPPA AAA” in Cell A39). I only desire the project grant number/ identifier (OA21680FY12) and not the project grant name / description. Note that this is the most difficult aspect of this project that I cannot figure out how to automate.<o></o>
· Then I sorted the data by Column B. All rows with G/L account numbers were then grouped together. I then deleted rows above and below my desired data (i.e. blank rows, rows with titles, rows with headings, rows with totals, etc.).<o></o>
· Then I deleted Columns E thru I as I do not need this data / these amounts.<o></o>
· Then I inserted two new columns between A and B and split the new Column D into these 2 new columns as follows:<o></o>
o G/L account number using =--LEFT(D1,6) in Col B<o></o>
o G/L account description using =RIGHT(D1,LEN(D1)-9) in Col C<o></o>
· Then I copied these 2 new columns (B & C)and pasted them as values<o></o>
· Then I deleted column D (the combined G/L account number and the account description)<o></o>
· Then I inserted headings / titles for thefinished data columns in Row 1 (PROJECT GRANT, ACCT #, ACCT DESCRIPTION, BUDGET, & EXPENSE)<o></o>
<o></o>
I will then be able to utilize this resulting data table in analyses, projections, reporting, etc.<o></o>
<o></o>
Additional info:<o></o>
· I am using Excel 2010<o></o>
· I consider myself advanced with Excel<o></o>
· I consider myself a novice with VBA (I can formulate and grasp the concepts / logic - but I am very basic with coding)<o></o>
<o></o>
My goal is to automate this process as much as possible. In an ideal world I would generate the CSV file, import it into Excel, run a macro, and then the end result would be as shown on the Processed worksheet on the linked file.<o></o>
<o></o>
Any direction, assistance, VBA code, coaching, help, etc. would be greatly appreciated. Thank you!<o></o>
<o></o>

 

[Jeff Meyers]

Bump - Anyone willing to take a crack at it? Or at least a piece of it?

 

[Jeff Meyers]

Is anyone able to get me started on this?

The biggest piece that I would like to be able to tackle is to add the corresponding project grant number / identifier (i.e. OA21680FY12) to each blank cell in Col A. There are some cells in Col A that will not need this number / identifier, but I am not concerned with that as those one will "fall-out" on a subsequent sort.

So what the VBA logic needs to do for each blank cell in Col A is to go one cell to the right (Col B) and then search upwards for the first occurrence of a string that begins with "OA" and then copy that string (the first 11 characters or the entire string) to the blank cell in Col A. It then needs to move down to the next blank cell in Col A and repeat the process. This process should repeat for all rows for which there is a string in Col B.

Can anyone help out with this piece of the puzzle to get me started?

Thank you!

 

[Tinbendr]

OK, try this

Activecell must be on Grant Number.

The Like operator really does a lot of the heavy work.

Adds to the end of the last row on the Processed sheet.

Not much error checking. :P

Enjoy!

 

[Jeff Meyers]

David / Tinbendr

Thank you so much for tackling this project. I finally had a chance to go through it and test it this afternoon.

Here are my observations:

• It's not necessary to create a second worksheet (i.e. "Processed") if it's easier to do the work on the data worksheet (i.e. "CSV IMPORT"). Having a second worksheet is also not a problem.
• The biggest issue right now is that there will be (could be) multiple Grant Numbers on the CSV import. Your approach assumes 1 (one) Grant Number and associates it with all of the G/L Account Numbers in the report.
• There could be anywhere from 1 (one) to 40 (forty) Grant Numbers on the CSV import depending on how the file is generated. I need to associate each one with it's corresponding G/L Account Numbers.
  • On the sample file I uploaded there are 3 Grant Numbers (OA21680FY12 (see Cell B7), OA21681FY12 (see Cell B40), and OA21682FY12 (see Cell B51)).
  • There could be anywhere from 1 (one) to 50 (fifty) G/L Account Numbers associated with each specific Grant Number. These are listed in Column B after the Grant Numbers, so all G/L Account Numbers in Column B listed after OA21680FY12 should be associated with that Project Grant (i.e. Rows B9 thru B37 on the attached file).

The way that I envison the first step (insert the Grant Numbers with each of its associated G/L Account Numbers)of the VBA logic is as follows:

• For every cell in Column B that begins with 6 numbers (exactly and only 6 numerics) it should "grab" the first 11 characters from the next cell above in Column B that beings with "OA" and then insert these 11 characters in the adjacent cell to the left (Column A).
• It should then repeat this process until all cells in Column B have been evaluated.

• Examples for the attached workbook:
  • Based on Cell B9, the first 11 characters from Cell B7 ("OA21680FY12") should be pasted in Cell A9
  • Based on Cell B10, the first 11 characters from Cell B7 ("OA21680FY12") should be pasted in Cell A10
  • Based on Cell B11, the first 11 characters from Cell B7 ("OA21680FY12") should be pasted in Cell A11
  • . . .
  • . . .
  • Based on Cell B42, the first 11 characters from Cell B40 ("OA21681FY12") should be pasted in Cell A42
  • Based on Cell B43, the first 11 characters from Cell B40 ("OA21681FY12") should be pasted in Cell A43
  • . . .
  • . . .
  • Based on Cell B53, the first 11 characters from Cell B51 ("OA21682FY12") should be pasted in Cell A53
  • Based on Cell B54, the first 11 characters from Cell B51 ("OA21682FY12") should be pasted in Cell A54
  • . . .
  • Based on Cell B85, the first 11 characters from Cell B51 ("OA21682FY12") should be pasted in Cell A85

Thank you again for your time and efforts with this project. Any ideas on revising / updating the code?

I have re-uploaded the test file to Box.com (I had inserted a text function in one of the cells in Column A when I was experimenting with the file. It has been removed in this uploaded file to be just as it was when originally imported).

File link to download: https://www.box.com/s/9ac3171c112f67c51319

 

[Tinbendr]

OK, I see. Then would you update your file one more time. Delete the Processed page and add a page that is formatted to your requirements.

 

[Jeff Meyers]

Tinbender - What I described in Post #5 is the first step in the process - inserting the respective Grant Numbers on the same rows with each G/L Account number to have complete data records.

I'm not sure what you mean by "Then would you update your file one more time." I would then proceed to sort, delete, and edit my data to have the resulting data table. I do not necessarily need to delete any pages / worksheets - all data manipulation could take place on the one "CSV IMPORT" page.

The only reason that I have two worksheets on the sample file is to show what the data looks like coming in, and how I would like it to look at the end.

Thank you.

 

[Tinbendr]

It seems you've u/l to one I u/l earlier.

 

[Jeff Meyers]

It seems you've u/l to one I u/l earlier.

No, the file I uploaded is not the one you uploaded earlier.h

Tis is the file that I originally uploaded but with one change: when I uploaded it the first time I had a formula in one cell (A11 I believe) on the "CSV Import" worksheet that should not have been there. I want the "CSV Import" worksheet to be just as I imported it into Excel. So I deleted the formula, uploaded the file to Box.com, and then posted the link (in Post #5 of this thread) to the file.