• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Macro to delete all the rows where the values total zero|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / Macro to delete all the rows where the values total zero|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosMacro to delete all the rows where …
sp_PrintTopic sp_TopicIcon
Macro to delete all the rows where the values total zero
Avatar
Howard Cohen
Member
Members
Level 0
Forum Posts: 48
Member Since:
December 8, 2016
sp_UserOfflineSmall Offline
1
June 25, 2021 - 3:14 am
sp_Permalink sp_Print

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
June 25, 2021 - 4:02 pm
sp_Permalink sp_Print

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.

Avatar
Howard Cohen
Member
Members
Level 0
Forum Posts: 48
Member Since:
December 8, 2016
sp_UserOfflineSmall Offline
3
June 25, 2021 - 8:04 pm
sp_Permalink sp_Print

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

 

 

 

    

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
4
June 25, 2021 - 8:27 pm
sp_Permalink sp_Print

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
5
June 25, 2021 - 9:20 pm
sp_Permalink sp_Print

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.

Avatar
Howard Cohen
Member
Members
Level 0
Forum Posts: 48
Member Since:
December 8, 2016
sp_UserOfflineSmall Offline
6
June 25, 2021 - 9:33 pm
sp_Permalink sp_Print sp_EditHistory

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

Avatar
Howard Cohen
Member
Members
Level 0
Forum Posts: 48
Member Since:
December 8, 2016
sp_UserOfflineSmall Offline
7
June 27, 2021 - 1:56 pm
sp_Permalink sp_Print

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
8
June 27, 2021 - 3:08 pm
sp_Permalink sp_Print sp_EditHistory

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?

Avatar
Howard Cohen
Member
Members
Level 0
Forum Posts: 48
Member Since:
December 8, 2016
sp_UserOfflineSmall Offline
9
June 27, 2021 - 4:03 pm
sp_Permalink sp_Print

Hi Catalin

 

Please find original Data. Hope this helps

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
10
June 28, 2021 - 12:25 am
sp_Permalink sp_Print

Not seeing anything usable unfortunately, sorry.

Avatar
Howard Cohen
Member
Members
Level 0
Forum Posts: 48
Member Since:
December 8, 2016
sp_UserOfflineSmall Offline
11
June 28, 2021 - 1:27 am
sp_Permalink sp_Print sp_EditHistory

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
12
June 28, 2021 - 3:03 am
sp_Permalink sp_Print sp_EditHistory

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.

Avatar
Howard Cohen
Member
Members
Level 0
Forum Posts: 48
Member Since:
December 8, 2016
sp_UserOfflineSmall Offline
13
June 28, 2021 - 12:41 pm
sp_Permalink sp_Print

I agree with you 100%

Avatar
Howard Cohen
Member
Members
Level 0
Forum Posts: 48
Member Since:
December 8, 2016
sp_UserOfflineSmall Offline
14
June 30, 2021 - 12:43 pm
sp_Permalink sp_Print

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
15
July 1, 2021 - 2:48 pm
sp_Permalink sp_Print

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

Avatar
Howard Cohen
Member
Members
Level 0
Forum Posts: 48
Member Since:
December 8, 2016
sp_UserOfflineSmall Offline
16
July 1, 2021 - 9:22 pm
sp_Permalink sp_Print

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

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Aislinn Mellamphy, Lynnette Altomari, Jessica Stewart, Roy Lutke, Dieneba NDIAYE, Darrell Hodge, Tucker Oakley
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Sopi Yuniarti
sandra parker
LAFONSO HERNANDEZ
Hayden Hao
Angela chen
Sean Moore
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Forum Stats:
Groups: 3
Forums: 24
Topics: 6214
Posts: 27243

 

Member Stats:
Guest Posters: 49
Members: 31894
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.