Help with Macro to delete rows with duplicate numbers of opposite signs in the same column
I'm new to this forum and I would like some help with creating a macro. I am not familiar with macros. The attached file that I am testing has three columns: 1/ Date, 2/ Name, and 3/ Quantity. I would like to delete all rows that has the same person in Column B, and has a corresponding negative and positive amount in column C. For Example, in the attached file, "Phil" has three entries in column B. However, only two of the tree entries would be deleted since there is only one positive amount and a corresponding negative amount in column C. The file that I will eventually use the macro with has over 100,000 records. Could you help me please?
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!!!
According to the - too light - attachment and the records # as a VBA starter :
Const S = "&""¤""&"
Dim V, W, D%(), U, R&, N&
With Sheet1.UsedRange.Rows("2:" & Sheet1.UsedRange.Rows.Count).Resize(, 4).Columns
V = .Parent.Evaluate(.Item(2).Address & S & .Item(3).Address)
W = .Parent.Evaluate(.Item(2).Address & S & "-" & .Item(3).Address)
ReDim D(1 To UBound(W), 0)
For Each U In V: .Item(U) = .Item(U) + 1: Next
For R = 1 To UBound(W)
If .Exists(W(R, 1)) Then
D(R, 0) = 1: N = .Item(W(R, 1)) - 1
If N Then .Item(W(R, 1)) = N Else .Remove W(R, 1)
N = Application.Sum(D) - 1
If N Then
Application.ScreenUpdating = False
.Item(4) = D
.Sort .Item(4), xlAscending, Header:=xlNo
Union(.Rows(UBound(W) - N & ":" & UBound(W)), .Item(4)).Clear
Application.ScreenUpdating = True
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄