I have a quite odd question that I'm trying to figure out a way to do it in Excel, but I'm not sure it is possible.
I have a report file of about 3,000 charges. Normally when this file is loaded into our system it is formatted in a way that each person is only listed once under one main account number with a balance of their combined charges. However, when the last person ran this report they did not select the proper report options so each charge was listed individually under a shared account number.
We now must correct this, but because some time has passed and certain accounts have had payments made on them we cannot just close them all and reload them properly. What has to be done is we have to close the charge #'s that no payments have been made on and then add those balances to the accounts where payments have been made. I cannot simply sum the balances and add that to the accounts because this would happen:
Person A has three charges #'s each with a $25 balance, meaning they owe a total of $75. The system currently shows these three charge #'s with their proper balances, but I need 1 charge # showing a total of $75.
If I were to sum the totals and then run it through the system, the program would add the total due ($75) to the current balance ($25), and the person would then show a total due of $100.
Some people may be showing twice in the file, others may be showing as many as 20 times. Each of these charges shares the same account number, so I'm using that as my identifying factor.
My question is: Is there a way to sum items together, but remove a certain dynamic value?
I may need to sum cells B3:B5, but remove B3's value from the total?
Thanks.
I have a report file of about 3,000 charges. Normally when this file is loaded into our system it is formatted in a way that each person is only listed once under one main account number with a balance of their combined charges. However, when the last person ran this report they did not select the proper report options so each charge was listed individually under a shared account number.
We now must correct this, but because some time has passed and certain accounts have had payments made on them we cannot just close them all and reload them properly. What has to be done is we have to close the charge #'s that no payments have been made on and then add those balances to the accounts where payments have been made. I cannot simply sum the balances and add that to the accounts because this would happen:
Person A has three charges #'s each with a $25 balance, meaning they owe a total of $75. The system currently shows these three charge #'s with their proper balances, but I need 1 charge # showing a total of $75.
If I were to sum the totals and then run it through the system, the program would add the total due ($75) to the current balance ($25), and the person would then show a total due of $100.
Some people may be showing twice in the file, others may be showing as many as 20 times. Each of these charges shares the same account number, so I'm using that as my identifying factor.
My question is: Is there a way to sum items together, but remove a certain dynamic value?
I may need to sum cells B3:B5, but remove B3's value from the total?
Thanks.
