looking for repetitive information in a report

[edluver]

I am currently working on a macro that formats a system generated report by
color coordinating accounts to prioritize what should be worked on first. I
am running into a couple of problems that could be solved if i knew how to do
the following: I would like help writting code that would check to see if an
account number and credit *i will give an example of what i mean* have
appeared before. I would attach a section of the report, but i dont know how
because once i paste, it is useless information without cells, so i will do
my best to explain.
A B C D
E F
Comments Bank PrimaryAccntNo OpenDate AvgBal TranType
nh - balance 31 156123123 3/06/07 $300.00
C
w 31 156123123 3/06/07 $300.00
C
w 31 156123123 3/06/07 $300.00
D
46 123123123 3/06/07 $900.00
C
46 123123123 3/06/07 $900.00
D

There will ALWAYS be at least one C in the TranType column and at least one
D. There can be multiple C's and D's (credits (deposits) and debits
(checks)) for each account number. What i need is a macro that will
determine if a credit has appeared for the same account before, and if so, it
will take all deposited item amounts *not shown due to space constraints* and
add them together, compare that total with average that can be found in each
line, and then if certin conditions are met (in this macro, if the average
balance is less than two times the combined total of deposited items than one
type of comment is placed in column A corrisponding to the first line
containing the primaryaccount number being worked [nh - balance], and a
different comment in each line following the first one[ w ] *i have given an
example of what i want above*). I really hope someone can help me. If i
have been too vague or if you need more info, please, let me know and i will
provide it. Thanks.

 

[David Portwood]

I'm at home responding from memory without my Excel book for reference, so
check me on specifics. The following are some strategic thoughts:

If the table is sorted by Account number, then average balance can be found
by using =SUMIF(from first row) / COUNTIF(from first row) for that account
number. Actually, I think you may need to use DSUM and DCOUNT. It sounds
like all of your formulas can be determined using some combination of DSUM,
DCOUNT and DAVERAGE, combined with =IIF().

Of course, these would all be embedded UDF's. I'm not seeing a need for a
macro at all.

 

[edluver]

the entire macro itself is much more involved. this is only one function it
would perform of many that it already carries out. I really appreciate your
help with this, but i guess i am just slow on the uptake, how would i put
that into macro form? i am new at macros, so i appreciate your patience, i
really do...

I'm at home responding from memory without my Excel book for reference, so
check me on specifics. The following are some strategic thoughts:

If the table is sorted by Account number, then average balance can be found
by using =SUMIF(from first row) / COUNTIF(from first row) for that account
number. Actually, I think you may need to use DSUM and DCOUNT. It sounds
like all of your formulas can be determined using some combination of DSUM,
DCOUNT and DAVERAGE, combined with =IIF().

Of course, these would all be embedded UDF's. I'm not seeing a need for a
macro at all.

I am currently working on a macro that formats a system generated report by
color coordinating accounts to prioritize what should be worked on first.
I
am running into a couple of problems that could be solved if i knew how to
do
the following: I would like help writting code that would check to see if
an
account number and credit *i will give an example of what i mean* have
appeared before. I would attach a section of the report, but i dont know
how
because once i paste, it is useless information without cells, so i will
do
my best to explain.
A B C D
E F
Comments Bank PrimaryAccntNo OpenDate AvgBal TranType
nh - balance 31 156123123 3/06/07 $300.00
C
w 31 156123123 3/06/07 $300.00
C
w 31 156123123 3/06/07 $300.00
D
46 123123123 3/06/07 $900.00
C
46 123123123 3/06/07 $900.00
D

There will ALWAYS be at least one C in the TranType column and at least
one
D. There can be multiple C's and D's (credits (deposits) and debits
(checks)) for each account number. What i need is a macro that will
determine if a credit has appeared for the same account before, and if so,
it
will take all deposited item amounts *not shown due to space constraints*
and
add them together, compare that total with average that can be found in
each
line, and then if certin conditions are met (in this macro, if the average
balance is less than two times the combined total of deposited items than
one
type of comment is placed in column A corrisponding to the first line
containing the primaryaccount number being worked [nh - balance], and a
different comment in each line following the first one[ w ] *i have given
an
example of what i want above*). I really hope someone can help me. If i
have been too vague or if you need more info, please, let me know and i
will
provide it. Thanks.