Parse thru a CSV file, subtotal, and remove records based on the subtotal amount. Output to a new excel file. Implement using an Excel VBA macro from the output file.
Example of the CSV file:
Type,Date,Num,Name,Memo,Account,Clr,Split,Amount,Balance
Deposit,6/12/2008,0,01-002741 Schumann,Deposit,impounds,,,473.4,3322.76
Deposit,6/12/2008,737,05-002005 Diaz,Deposit,appraisal,,,375,475
Deposit,6/12/2008,1043,33-000412 Quintero,Deposit,appraisal,,,400,875
Deposit,6/12/2008,103,33-000440 Bradley,Deposit,appraisal,,,400,1275
Check,6/23/2008,wire,01-002741 Schumann,,impounds,,,-473.4,71003.07
…
## Deliverables
Attached is a ZIP file containing 2 files:
1) [login to view URL] - a sample of data
2) [login to view URL] - a sample of the final output based on the sample [login to view URL]
Here's the intended workflow:
1) We export data from another software data into the [login to view URL] file.
2) We open the [login to view URL] file (in the same folder as [login to view URL])
3) We click a button in the [login to view URL] file
4) The macro does it's stuff resulting in the output.
Here's what the macro needs to do
1) Remove the last row in the [login to view URL] file. (The row that has the word total in column A).
2) Sort the data in the [login to view URL] file by the “Name?? column, Subtotal the “Amount?? column on change in the “Name?? column.
2) Remove all records for a given “Name?? which subtotal is zero. (including the subtotals)
3) Erase any remaining data in the [login to view URL] file
4) Output the remaining records and subtotals from Steps 1 and 2 into the [login to view URL] file.
5) Remove the “Balance?? column from the [login to view URL] file
5) Bold Every Row containing a subtotal.
6) Set the format of column J to currency.
5) Output in the same excel file at the bottom a subtotal by the “Amount?? column on the change in the “Account?? column.