Log In
Register
Home
December 8, 2016
Offline
I have debit and credit values in Col H. I am looking for VBA to delete all the rows where the values sum up to zero
I have highlighted the values that add up to zero using a blue font
I have showing the items not to be delete on a seperate sheet for ease of reference
Your assistance is most appreciated
I have also posted on https://www.excelforum.com/exc.....col-h.html
November 8, 2013
Offline
Hi Howard,
The values that will sum to 0 will always be consecutive? For example, code should start from row 2 and stop when moving sum plus next row is zero?
If you intend to combine rows in all possible ways to get a zero, that's hard.
By the way, the sum of the blue cells is 0.00000000000198951966012828, not 0.
December 8, 2016
Offline
Hi Catalin
Thanks for the reply.
One may have consecutive values balancing to zero for eg H2 could be 10000 and H3 -10000. You may also have situations where for e.g. H3 is 4000, h4 is 7500 and H15 is -11500 , then these rows must be deleted
the only items left should be those that do not sum to zero rounded to 2 decimal places
November 8, 2013
Offline
Have you tried this formula?
https://www.myonlinetraininghu.....on-formula
November 8, 2013
Offline
However, that formula works for same debit-credit amounts.
Without a unique identifier that ties the credit transactions to that specific debit transactions, it’s almost impossible, you have to dig through all transactions, combine the credit values until one debit transaction matches. And that is most likely a source of errors, as we don't know if a debit transaction has 1 or 100 credit amounts.
Let's take 1 case:
5 debit amounts: 1 of 1000 and 4 of 250. Total debits: 2000.
We might have these credit amounts, totaling 2000:
1:250,
2:300,
3:200,
4:250,
5:250,
6:500,
7:250
As there is no rule that limits the number of credit amounts, the 1000 debit can be matched by adding 1,4,5,7, or 2,3,6, or 1,2,3,4, or more combinations.
The only way to match all 5 debits is to match 1000 with 2,3,6 and the other 4 debits with 1,4,5 and 7, but code cannot decide which combination is best.
December 8, 2016
Offline
its a great formula but it does not work as I may have 10 debits that equal or or more credits and vice versa . which may not follow in any sequence
December 8, 2016
Offline
I had another thought.
Would it be possible to extract those items on a separate sheet that sums up to the total ? If so kindly provide me with the code
I have manually extracted these
November 8, 2013
Offline
What's the difference?
Still needs to combine 1 to many credits to identify a debit.
The only notable difference is that now your Data Ref column is NOT empty, but there are some inconsistencies:
Data Ref 14396 has 2 credits, combined credits matches the 1081.46 debit.
But: there are 2 credits with 288872-data ref, where each credit is matching 2 separate debits. This inconsistency makes Data Ref unusable.
To be of any use, as you have been told, you need to have identifiers. Data Ref might be usable but only if there are no inconsistencies like the one mentioned above.
Where do you get data from?
Can we see an original data set?
Why Data Ref is empty in one of your example files?
December 8, 2016
Offline
Hi Catalin
Please find original Data. Hope this helps
November 8, 2013
Offline
Not seeing anything usable unfortunately, sorry.
December 8, 2016
Offline
Thanks for all your input
Going forward going to get my staff to process one credit for every debit and vice versa and to use the same reference number where transaction is the same for eg Ref 123 for 1000 and 123 for -1000
This will make it easier to program
Regards
Howard
November 8, 2013
Offline
If you use the debit ref, you can have as many credits as needed for 1 debit, as long as all credits have the debit ref.
In the previous file sent, there was many debits with the same ref, which is not right.
December 8, 2016
Offline
I agree with you 100%
December 8, 2016
Offline
Hi Catalin
It would be appreciated if you write code that where the Narrative in Col I contains similar text within the narrative for e.g. "fuel" then to match the debits and credits for that category and if those items pertaining to those add up to zero and the text contains a common item for eg fuel ,then to delete those rows ,
The data is processed in different companies so the reference numbers will seldom be the same as it relates to intercompany transactions so the only other alternative is for the staff to have similar text in the narrative
I have highlighted the items where the debits and credits match and the narrative containing a common item in the text
November 8, 2013
Offline
Hi Howard,
I still don't see a way to do it unfortunately.
More, in the latest file you sent, the problem is even more complex than before: there is a credit that matches multiple debits, before we had a match between a debit and multiple credits...
1 Guest(s)
