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

[ghrek]

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

 

[steve the fish]

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]

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]

Good point joe4. In Column D I have a date of issue. May be look at that too?

 

[Joe4]

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]

Shouldnt have same date on one or more entry. See part of workbook below

Sheet 1 (11).csv
	A	B	C	D	E	F	G
4	Data Recreated	2464	EK	16/09/2021 00:00	95140	6	£1,410.00
5	Data Recreated	2466	EK	06/09/2021 00:00	91594	6	£0.00
6	Data Recreated	2466	EK	06/09/2021 00:00	95055	6	-£139.60
7	Data Recreated	2466	EK	06/09/2021 00:00	95140	6	£139.60
8	Data Recreated	2466	EK	16/09/2021 00:00	0	6	-£16.70
9	Data Recreated	2466	EK	16/09/2021 00:00	91594	6	£0.00
10	Data Recreated	2466	EK	16/09/2021 00:00	95140	6	£16.70
11	Data Recreated	2466	EK	16/09/2021 00:00	95162	6	£0.00
Sheet 1 (11)

 

[Joe4]

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]

Codes in column E ignore and if you can match and delete the zeros it would be good but not important

 

[Joe4]

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:

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]

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?