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

ghrek

Active Member
Joined
Jul 29, 2005
Messages
317
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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,644
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,107
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
317
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,107
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
317
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,107
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
317
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,107
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
317
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?
 

Forum statistics

Threads
1,147,921
Messages
5,743,888
Members
423,823
Latest member
Zxcvbnm58

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