Untitled

Sub PairOffExceptions()
    Dim reconWorkbook As Workbook
    Dim exceptionsSheet As Worksheet
    Dim transactionsSheet As Worksheet
    Dim transactionList As Collection
    Dim lastTransactionRow As Long
    Dim transactionRow As Long
    Dim currentTransaction As Variant
    Dim compareTransaction As Variant
    Dim offsetTolerance As Double
    Dim i As Long, j As Long, k As Long
    Dim cusip As String, paydownValue As Double, gValue As Double, hValue As Double
    Dim foundG As Boolean, foundH As Boolean
    Dim sumOfComponents As Double, componentCount As Integer

    ' Initialize workbook and sheets
    Set reconWorkbook = ThisWorkbook
    Set exceptionsSheet = reconWorkbook.Sheets("exceptions")
    Set transactionsSheet = reconWorkbook.Sheets("transactions")

    ' Initialize collection for in-memory storage
    Set transactionList = New Collection

    ' Define offset tolerance
    offsetTolerance = 0.02

    ' Load transactions into memory, filtering by "PAYDOWN RECEIVED" and specific transaction types
    lastTransactionRow = transactionsSheet.Cells(transactionsSheet.Rows.Count, "A").End(xlUp).Row
    For transactionRow = 2 To lastTransactionRow
        If transactionsSheet.Cells(transactionRow, "A").Value = "6QFG" Or transactionsSheet.Cells(transactionRow, "A").Value = "RUSECP" Then
            Dim transData(0 To 7) As Variant ' Array to store transaction details
            transData(0) = transactionsSheet.Cells(transactionRow, "A").Value ' Column A (Transaction ID)
            transData(1) = transactionsSheet.Cells(transactionRow, "D").Value ' Column D (CUSIP)
            transData(2) = transactionsSheet.Cells(transactionRow, "F").Value ' Column F (Amount)
            transData(3) = transactionsSheet.Cells(transactionRow, "B").Value ' Column B (Source)
            transData(4) = transactionsSheet.Cells(transactionRow, "E").Value ' Column E (Description)
            transData(5) = transactionsSheet.Cells(transactionRow, "G").Value ' Column G (Principle)
            transData(6) = transactionsSheet.Cells(transactionRow, "H").Value ' Column H (Interest)
            transData(7) = False ' Processed flag
            transactionList.Add transData ' Add the transaction to the list
        End If
    Next transactionRow

    ' Iterate through transactions in memory
    For i = 1 To transactionList.Count
        currentTransaction = transactionList(i)

        ' Check for "PAYDOWN RECEIVED" in column E and if the transaction hasn't been processed
        If currentTransaction(4) = "PAYDOWN RECEIVED" And currentTransaction(7) = False Then
            cusip = currentTransaction(1)
            paydownValue = currentTransaction(2)
            gValue = currentTransaction(5)
            hValue = currentTransaction(6)
            
            foundG = False
            foundH = False
            sumOfComponents = 0
            componentCount = 0
            
            ' Loop through remaining transactions to find matches for G and H
            For j = 1 To transactionList.Count
                compareTransaction = transactionList(j)
                
                If j <> i And compareTransaction(7) = False And compareTransaction(1) = cusip Then
                    Dim compareAmount As Double
                    compareAmount = compareTransaction(2)
                    
                    ' Check for G and H component matching within tolerance
                    If Abs(compareAmount + gValue) <= offsetTolerance Then
                        foundG = True
                        sumOfComponents = sumOfComponents + compareAmount
                        transactionList(j)(7) = True
                        componentCount = componentCount + 1
                    ElseIf Abs(compareAmount + hValue) <= offsetTolerance Then
                        foundH = True
                        sumOfComponents = sumOfComponents + compareAmount
                        transactionList(j)(7) = True
                        componentCount = componentCount + 1
                    End If
                    
                    ' If both components match and sum close to paydown value, mark as processed
                    If foundG And foundH And Abs(paydownValue + sumOfComponents) <= offsetTolerance Then
                        currentTransaction(7) = True
                        Exit For
                    End If
                End If
            Next j
            
            ' If all components matched, delete rows in reverse order
            If foundG And foundH And Abs(paydownValue + sumOfComponents) <= offsetTolerance Then
                exceptionsSheet.Rows(i + 1).Delete
                For k = transactionList.Count To 1 Step -1
                    If transactionList(k)(7) = True Then
                        exceptionsSheet.Rows(k + 1).Delete
                    End If
                Next k
            End If
        End If
    Next i

    MsgBox "Pair-off exceptions complete!", vbInformation
End Sub