I have balance in Col H and need the rows to be delete where
1) The values are opposite and equal for eg -100, 100
2) Where balances in Col H add up to zero
I have highlighted those items that need to be deleted (yellow where the value are opposite and equal and blue where several values add up to zero)
it would be appreciated if someone could provide me with code to do this
This has worked for large datasets...
Please Login or Register to view this content.
Good Luck
I don't presume to know what I am doing, however, just like you, I too started somewhere...
One-day, One-problem at a time!!!
If you feel I have helped, please click on the star to left of post [Add Reputation]
Also....add a comment if you like!!!!
And remember...Mark Thread as Solved.
Excel Forum Rocks!!!
Thanks very much Sintek Code works perfectly
All good...happy to help...
hi Sintek
I ran your macro on the attached sample data and where the debits and credit sum to zero, these ae not being deleted
I have highlighted the items in blue that should remain as these do not add up to zero
one can have for eg 10 debit vslues in Col H adding up to say 15001.25 and one or several value adding up to -15001.25 -All these items must be deleted and only the items tat do not sum up to zero are to remain
It would be appreciated if you would kindly amend your code
thanks
Howard
Last edited by Howardc1001; 06-23-2021 at 11:01 AM.
Hi Howard
Don't have any time to go into this at the moment...But offhand...See file...All yellow...Your first Value of 33011.4 does have values that balance to Zero
Hi Sintek
thanks for your prompt response
The items in yellow do sum to zero, but when running the macro only the items in blue per my previous post should remain as al the other values sum to zero
There is no urgent rush. Whenever you have a chance, kindly have a look at this for me
You have always been a great help
As per previous post...The first item in blue which you say should remain...33011.4 does have other values that add up to zero...hence this should not remain???but when running the macro only the items in blue per my previous post should remain as al the other values sum to zero
My Apologies. You are 100% correct
The only items that must remain are those that do not add up to zero
So...upload a sample of before and correct expected after result please...Will have a look in the morning...
I have upload sample file. See Sheet Remaining Items that do not sum to zero
Howard...look at this file attached...You say that there are no entries that add up to zero for R8900...But the yellow all cancel the 8900
I think there is a flaw in your actual data or perhaps your requirement has more detail which I am unaware of...
Hi Sintek
My apologies. Not sure how this happening , I have now uploaded a new sample file and there should only be two items that do now add up to zero (rows 85 & 86 on sheet1)
Your assistance is most appreciated
Last edited by Howardc1001; 06-24-2021 at 05:58 AM.
Nope...Don't get it...You say that those 2 rows are the only ones that do not have values to cancel them out...
I changed code to incorporate color coding...
I stepped through your data and this(ALL DATA WITHOUT COLOR CODING) is the values left that do not have (-) to cancel out???
Last edited by sintek; 06-24-2021 at 07:54 AM.
If you add the total of Col h, it totals -11118.6 on sheet1
all the items that add up to zero in Col H (have set a blue font for these) must be deleted
The values in Grey are to remain as they do not sum to zero
Your initial requirement was Cancelling out specific Debits with Credits that cancel said Debit out...
This means one should start with said debits...run through and cancel out credits that match said debit...then move to next and so forth...
What you are wanting now...Just makes no sense to me...Perhaps someone else can solve...Apologies...Good luck...
I suggest you start with Totaling Debits & Totaling Credits...Get Difference...
Find values that add up to difference and delete all except those values...
Last edited by sintek; 06-24-2021 at 10:15 AM.
Thanks for your suggestion Sintek
the value in Col, H is the net amount
what I am looking to achieve is delete all the rows the sum to zero leaving only these items that do not sum up to zero
Last edited by Howardc1001; 06-25-2021 at 07:43 AM.
Hi Sintek
I have come up with a solution where your code will work
It boils down to when my staff processing corresponding entries that they process individual entries for each corresponding value for e.g if there is 5 debits , then they to process 5 individual credits and not just one
Many thanks for your valuable input
Generally, when a credit is passed, it carries with it a corresponding reference which can be offset against same debit...
Your latest sample files provided just have a large amount of debits and credits...No corresponding reference requirements...Expecting to offset this in some way just seems impossible as there could be multiple variances, scenarios and outcomes...As proved with your expected results wanted...
Your initial file however, had a few debits and 1 to 3 credits which balance each other out...Hence code to look at Individual debits and find the single| multiple credits which balance said amount out...This is do-able...
You are 100% correct that there should be corresponding references to be offset against the debits and credits
It boils down to poor discipline which I need the staff to attend to
Not sure how your system is coded, but I suggest you should incorporate code that ensures a reference is entered...Prior, code should also check if said reference is available else a msg to say...ref does not exist which will ensure correct debit credit entries...
This way, credits can very easily be offset with debits and c|f balances can be very accurate...Anyway, enjoy...Happy coding...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks