Forum Discussion
Excel, Ledger, Zum to Zero
Hello Every one,
I am looking for some help to reduce the amount of time it takes me to reconcile an account. I have entries with positive and negative amounts in one column, I need to able to find out if there is a group of those numbers when added together equals 0. I can do it it manually and it takes way longer.
I have a column with negative and positive numbers, its probably 1000's of rows. I need to be able to remove any sum of numbers that are equal to Zero. Meaning if i have the numbers (5,15,30,-45), it would be able to highlight that 15,30,-45 equal to zero.
You've had 54 View as of this writing, 0 Replies. I suspect it's because so much more information is needed. Could you back up a bit and describe more fully (more details perhaps) what this ledger is, how many entries you're talking about, and what reconciliation means in this case. It seems an odd request on the surface (which may also be why nobody has replied)....
How much flexibility do you have to make changes to the whole process? Presumably these are charges out, payments in, or something like that....could they be (or are they) coded in some way so that you could add the invoiced/billed amount(s) to the payment(s) received amount for each individual transaction or customer?(In fact, in the absence of some kind of coding of each entry, you'd run the risk of "reconciling" but not accounting accurately for payments by any given customer (or whatever the entities are). Simple data integrity, or integrity of the process, would seem to demand more than just finding an arbitrary grouping of positive and negative numbers that add up to zero.)
- Hi Mathetes,
Thank you for your reply, an arbitrary grouping of positive and negative numbers that add up to zero is exactly what I need. this account that I am trying to reconcile is like a catch all account, so everything that doesn't match gets dumped in it.You've written:
I have a column with negative and positive numbers, its probably 1000's of rows. I need to be able to remove any sum of numbers that are equal to Zero. Meaning if i have the numbers (5,15,30,-45), it would be able to highlight that 15,30,-45 equal to zero.
And I'm sorry, but that didn't clarify anything. It just illustrated what was obvious in your first post.
And then in replying to my first set of questions and observations, you wrote:
Thank you for your reply, an arbitrary grouping of positive and negative numbers that add up to zero is exactly what I need. this account that I am trying to reconcile is like a catch all account, so everything that doesn't match gets dumped in it.
And again, I'm sorry, but this still sounds like it could be a recipe for disaster. Could be. As in "not a sure thing," but a distinct possibility. The very fact that you accept an "arbitrary grouping" of the numbers concerns me. If we were sitting down face-to-face, I'd want to know the story behind this, rather than just to help do it. You've no doubt heard the old adage about computer programming, GIGO, meaning "Garbage In, Garbage Out"... To the extent that I worked with computer systems during my career, one of the values that was impressed on me was that of Data Integrity; it means a lot of things, but I have no recollection of hearing "arbitrary grouping" as one of the components.
If the Solver suggestion from Charla74 works for you, that's fine. But if you need more help, I personally would want to know more about the situation itself (I'm not trying to delve into things that may be proprietary or confidential, but I do want to do my best to assure we're doing things that make sense from a data integrity point of view).
