Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
looking for repetitive information in a report
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
looking for repetitive information in a report
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... ;) "David Portwood" wrote: 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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Making report in WORD file by using information from Pivot table | New Users to Excel | |||
looking for repetitive information | Excel Programming | |||
Repetitive keystroke | Excel Discussion (Misc queries) | |||
repetitive | Excel Discussion (Misc queries) | |||
Repetitive IF THEN Statement | Excel Programming |