D
Damian Carrillo
I have been working my way through a project that checks for a travel
bill reconsilliation workbook (Without using filesearch, thanks to
Myrna Larson, Bob Phillips and Ron De Bruin, amongst others) on the
user's desktop, makes a backup copy, and reformats it into a CMS
Invoice import file.
The file conversion process has four steps, but the second one is the
only one I can't figure out how to automate:
A. The first step merges multiple spreadsheets together (Thank you,
David McRitchie for your massive guides), inserts a custom key/error
field (using my own custom TravelBillAnalysis function, the key merges
values from columns A,G,and J) and resorts the data based on the KEY
field values. The objective of this is to make all records flagged as
errors bubble up to the top. (Using Chip Pearson's delightful
ExtractElement function)
B. The second step hides the error rows and subtotals the AMOUNT field
based on changes in the KEY field. I need a subtotal of both where the
AMOUNT field equals the key field, but I also need to subtotal Fees
WITHIN those subtotals, where column 4 contains a given string "Fees"
or begins with "ARC". This much I am able to do with Excel's subtotal
tool and some complex formulas.
THE BIG PROBLEM!
================
The end-users demand the data appears in a certain format because they
must still manually modify it after this second step. I have to add
two columns in the middle of the data set, one for FEES and one for the
transaction TOTAL. I then have to move the subtotal information from
the inserted subtotal rows to the appropriate column in-line with the
last detail line before the subtotal row. Then I have to delete both
the empty subtotal rows and all other rows besides the last one in each
subtotal set where we want to move our subtotal information. (Does
that make sense?!) This In-line subtotal requirement is the part that
is killing me.
I've tried numerous combinations of formulas, subtotals, subsorts, etc
etc but I can't it to work. I've tried reversing the process to copy
down the data into the subtotal line using James Cone's
"FillInSubTotalBlanks()" subroutine, but then I can't figure out how to
target the correct lines to delete.
http://groups-beta.google.com/group...nSubTotalBlanks&rnum=1&hl=en#8e7f161bbeae5260
Since the amount of data in the file varies each month, I have to keep
all of the reference ranges flexible, but subroutines I've found or
written for activecell movement and range selection get messed up when
the subtotal lines are created while the auto filter is engaged.
Any help would be greatly appreciated!
bill reconsilliation workbook (Without using filesearch, thanks to
Myrna Larson, Bob Phillips and Ron De Bruin, amongst others) on the
user's desktop, makes a backup copy, and reformats it into a CMS
Invoice import file.
The file conversion process has four steps, but the second one is the
only one I can't figure out how to automate:
A. The first step merges multiple spreadsheets together (Thank you,
David McRitchie for your massive guides), inserts a custom key/error
field (using my own custom TravelBillAnalysis function, the key merges
values from columns A,G,and J) and resorts the data based on the KEY
field values. The objective of this is to make all records flagged as
errors bubble up to the top. (Using Chip Pearson's delightful
ExtractElement function)
B. The second step hides the error rows and subtotals the AMOUNT field
based on changes in the KEY field. I need a subtotal of both where the
AMOUNT field equals the key field, but I also need to subtotal Fees
WITHIN those subtotals, where column 4 contains a given string "Fees"
or begins with "ARC". This much I am able to do with Excel's subtotal
tool and some complex formulas.
THE BIG PROBLEM!
================
The end-users demand the data appears in a certain format because they
must still manually modify it after this second step. I have to add
two columns in the middle of the data set, one for FEES and one for the
transaction TOTAL. I then have to move the subtotal information from
the inserted subtotal rows to the appropriate column in-line with the
last detail line before the subtotal row. Then I have to delete both
the empty subtotal rows and all other rows besides the last one in each
subtotal set where we want to move our subtotal information. (Does
that make sense?!) This In-line subtotal requirement is the part that
is killing me.
I've tried numerous combinations of formulas, subtotals, subsorts, etc
etc but I can't it to work. I've tried reversing the process to copy
down the data into the subtotal line using James Cone's
"FillInSubTotalBlanks()" subroutine, but then I can't figure out how to
target the correct lines to delete.
http://groups-beta.google.com/group...nSubTotalBlanks&rnum=1&hl=en#8e7f161bbeae5260
Since the amount of data in the file varies each month, I have to keep
all of the reference ranges flexible, but subroutines I've found or
written for activecell movement and range selection get messed up when
the subtotal lines are created while the auto filter is engaged.
Any help would be greatly appreciated!