Example Transaction Set:
Note the 3 transactions shown. The two transactions highlighted in green are the ones I'd like to delete or flag (doesn't matter).
Using VBA, the code is way too clunky. The code takes forever to run because
- I loop through each transaction in column C until I find a negative.
- Set Column E as the absolute value target if not zero, else absolute value D.
- Define the range for a specific account so that I can begin looping through it to find value in step 2.
- If I find the value, I delete both rows (one containing negative and the one which isn't a negative).
My apologies for not commenting code. This was not 100% done yet.
Sub ReversalScrub() Dim AccountNumber As String Dim TargetAmount As Double Dim TargetRange As Range Dim Transactions As Range Dim Transaction As Variant Dim DeletionCount As Integer Set RawTransactions = Worksheets("RawTransactions") With RawTransactions Set Transactions = .Range("C1", .Range("C2").End(xlDown)) End With TransactionRow = 2 Do Until TransactionRow = Transactions.Rows.Count If Range("C" & TransactionRow).Value < 0 Then If Range("C" & TransactionRow).Offset(0, 2).Value < 0 Then TargetAmount = Abs(Range("C" & TransactionRow).Offset(0, 2).Value) Else TargetAmount = Abs(Range("C" & TransactionRow).Offset(0, 1).Value) End If AccountNumber = Range("C" & TransactionRow).Offset(0, -2).Value Set TargetRange = GetAccountRange(AccountNumber, RawTransactions) CurrentRow = TargetRange.Row Do Until CurrentRow = TargetRange.Rows.Count - 1 If (TargetAmount = Range("E" & CurrentRow).Value Or TargetAmount = Range("D" & CurrentRow).Value) Then Range("A" & CurrentRow).EntireRow.Delete Range("C" & TransactionRow).EntireRow.Delete CurrentRow = CurrentRow - 2 Exit Do End If CurrentRow = CurrentRow + 1 Loop End If TransactionRow = TransactionRow + 1 Loop End Sub
So then I decided to write a function to see if it would be faster.
- Column F: Check if the transaction is negative. If so, create a key using account number, abs(column D), abs(column E).
- Column G: Create a key account number, column D, Column E.
- Column H: Check whether column F exists within G using
- Column I: Check whether actual cell row, matches that of column H from step.
- Column J: Check whether H or I are number (
MATCHoutput), if so, they are flagged as reversals and the user can delete them.
The problem is that this, too, crashes my computer. How can I effectively find duplicates and remove them without removing the third row?
Second solution sample: