Excel Forum

Macro to delete all the rows where the values total zero

Howard Cohen

Member

Members

Forum Posts: 56

Member Since:
December 8, 2016

Offline

June 25, 2021 - 3:14 am

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

Catalin Bombea

Iasi, Romania

Member

Dashboards

Power Query

Power Pivot

Xtreme Pivot Tables

Excel for Decision Making

Excel for Finance

Excel Analysis Toolpak

Power BI

Excel

Word

Outlook

Excel Expert

Excel Customer Service

PowerPoint

Forum Posts: 1944

Member Since:
November 8, 2013

Offline

June 25, 2021 - 4:02 pm

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.

Howard Cohen

Member

Members

Forum Posts: 56

Member Since:
December 8, 2016

Offline

June 25, 2021 - 8:04 pm

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

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1944

Member Since:
November 8, 2013

Offline

June 25, 2021 - 8:27 pm

Have you tried this formula?
https://www.myonlinetraininghu.....on-formula

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1944

Member Since:
November 8, 2013

Offline

June 25, 2021 - 9:20 pm

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.

Howard Cohen

Member

Members

Forum Posts: 56

Member Since:
December 8, 2016

Offline

June 25, 2021 - 9:33 pm

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

Howard Cohen

Member

Members

Forum Posts: 56

Member Since:
December 8, 2016

Offline

June 27, 2021 - 1:56 pm

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

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1944

Member Since:
November 8, 2013

Offline

June 27, 2021 - 3:08 pm

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?

Howard Cohen

Member

Members

Forum Posts: 56

Member Since:
December 8, 2016

Offline

June 27, 2021 - 4:03 pm

Hi Catalin

Please find original Data. Hope this helps

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1944

Member Since:
November 8, 2013

Offline

June 28, 2021 - 12:25 am

Not seeing anything usable unfortunately, sorry.

Howard Cohen

Member

Members

Forum Posts: 56

Member Since:
December 8, 2016

Offline

June 28, 2021 - 1:27 am

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

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1944

Member Since:
November 8, 2013

Offline

June 28, 2021 - 3:03 am

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.

Howard Cohen

Member

Members

Forum Posts: 56

Member Since:
December 8, 2016

Offline

June 28, 2021 - 12:41 pm

I agree with you 100%

Howard Cohen

Member

Members

Forum Posts: 56

Member Since:
December 8, 2016

Offline

June 30, 2021 - 12:43 pm

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

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1944

Member Since:
November 8, 2013

Offline

July 1, 2021 - 2:48 pm

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...

Howard Cohen

Member

Members

Forum Posts: 56

Member Since:
December 8, 2016

Offline

July 1, 2021 - 9:22 pm

hi Catalin

I agree it is too complicated

I am busy setting up a file which I will send to you tomorrow or over the weekend to match same and amount and to delete thoserows

Regards

Howard