How do I delete matching positive and negative entries in a column

ghrek

Active Member
Joined
Jul 29, 2005
Messages
319
Hi

In column G of my workbook I have lots of positive and negative values. Im trying to get it to look all the way down column G and if there is a matching positive and negative entry of the same value I need the rows they are on completely deleting.

Any Ideas?

Thanks
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,647
Office Version
  1. 365
Platform
  1. Windows
That sounds like an error waiting to happen but you could do something like this:

=IF(G1<>"",COUNTIFS(G:G,G1)=COUNTIFS(G:G,-G1),"")
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,755
Office Version
  1. 365
Platform
  1. Windows
You cannot delete rows with Excel formulas, so that almost assuredly requires VBA.

What gets really tricky, if what if you have multiple rows that match, i.e.?
-10
10
10

So, you have one row with -10, and two rows with 10.
How do you know which row of 10 to delete?
Or doesn't it matter which one you pick?
 

ghrek

Active Member
Joined
Jul 29, 2005
Messages
319
Good point joe4. In Column D I have a date of issue. May be look at that too?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,755
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

And what happens if they also happen to have the same date too (in the example of the 3 records I posted)?

And could you post an actual sample of the data, so we can see exactly what fields are there and how it is all structured?
 

ghrek

Active Member
Joined
Jul 29, 2005
Messages
319
Shouldnt have same date on one or more entry. See part of workbook below
Sheet 1 (11).csv
ABCDEFG
4Data Recreated2464EK16/09/2021 00:00951406£1,410.00
5Data Recreated2466EK06/09/2021 00:00915946£0.00
6Data Recreated2466EK06/09/2021 00:00950556-£139.60
7Data Recreated2466EK06/09/2021 00:00951406£139.60
8Data Recreated2466EK16/09/2021 00:0006-£16.70
9Data Recreated2466EK16/09/2021 00:00915946£0.00
10Data Recreated2466EK16/09/2021 00:00951406£16.70
11Data Recreated2466EK16/09/2021 00:00951626£0.00
Sheet 1 (11)
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,755
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Is thet date and the amount the only two values we need to match on?
What about the codes in column E?
And what about the zeroes? Should we ignore or try to match and delete them?
 

ghrek

Active Member
Joined
Jul 29, 2005
Messages
319
Codes in column E ignore and if you can match and delete the zeros it would be good but not important
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,755
Office Version
  1. 365
Platform
  1. Windows
Assuming that you have a header row in row 1, the following should delete all rows where the amount is 0, or there is exactly one matching positive and negative amount for the same day:
VBA Code:
Sub MyMatchDeleteMacro()

    Dim lr As Long
    Dim r As Long
    
'   Find last row in column D with data
    lr = Cells(Rows.Count, "D").End(xlUp).Row

'   Insert a blank column for column H
    Columns("H:H").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

'   Insert formula into column H until end of data
    Range("H2:H" & lr).FormulaR1C1 = _
        "=IF(RC[-1]=0,""DELETE"",IF(COUNTIFS(C[-4],RC[-4],C[-1],-RC[-1])=1,""DELETE"",""""))"

'   Convert formulas to hard-coded values
    Range("H2:H" & lr).Value = Range("H2:H" & lr).Value

'   Loop through all rows and delete marked rows
    Application.ScreenUpdating = False
    For r = lr To 2 Step -1
        If Cells(r, "H") = "DELETE" Then Rows(r).Delete
    Next r
    Application.ScreenUpdating = True
    
'   Delete temporary column H
    Columns("H:H").Delete Shift:=xlToLeft

End Sub
 

ghrek

Active Member
Joined
Jul 29, 2005
Messages
319
It appears to run but extremely slowly. Taken 90 min so far and not finished. Is it my excel on my PC or the macro?
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,578
Messages
5,765,203
Members
425,267
Latest member
bishopc22

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top