how to find what values sum a particular value

**anilahirwal** · 03-23-2011, 03:59 PM

Originally Posted by shg

With your 1857 numbers in col B, I found 6800+ solutions to 4 or fewer number totalling $5000.00. It was 38 seconds into the process, evaluating 1% of the possibilities when I shut it down. Do you see the problem?

Tx shg, for all ur kind efforts, i now understand the problem..............

but, to still reduce the manual search & match process, can a code in VBA be written 1. which searches a value , say $5000, in cell A5, matches the exact amount i.e. $5000 in column B somewhere, say B56. Then it deletes both the rows.i.e. row 5 & row 56
2. or if we go a bit further, 2 or 3 combinations in column b somewhere, for example, say for $5000, in cell A5, and $1000, $2500 & $1500 in say, cell no.s B10,B25&B65 respectively, it matches & all rows i.e. 5,10,25&65 are deleted.

This can kind of reduce few minutes of mine.

tx again for your kind help

**shg** · 03-23-2011, 05:17 PM

Deleting match values would be easy. Post a workbook.

**anilahirwal** · 03-24-2011, 02:09 PM

Originally Posted by shg

Deleting match values would be easy. Post a workbook.

posting my workbook match&sort.xls alongwith.

Column A & Column B contains the values, for which, i m trying to find the unmatched or unreconciled values, so that i can follow up properly.

by deleting the matched entries (single or match of few, say 2-3 or 4 values), i can reduce my daily search by hours.

very Thanks for kind response and efforts shg.

regards

**shg** · 03-24-2011, 08:00 PM

Delete the columns and formulas you added so your data looks like this:

       ---A---- ----B---- ----C----- ---D----
   1   tran_amt post_date value_date drcr_trn
   2    $-      20100423  20100423           
   3    $-      20100430  20100430           
   4    $12     20091230  20091230   C       
   5    $12     20091230  20091230   D       
   6    $14     20090519  20090519   C       
   7    $17     20090411  20090413   C       
   8    $20     20090713  20090713   C       
   9    $21     20100811  20100811   D       
  10    $24     20100907  20100907   D       
  11    $30     20100405  20100405   C       
  12    $30     20100518  20100518   C       
  13    $30     20100518  20100518   C       
  14    $31     20091104  20091104   C

(Actually that's what it looks like after it's sorted, which the code does.)

Then run DeleteMatchingTransactions

Option Explicit

Sub DeleteMatchingTransactions()
    With Intersect(ActiveSheet.UsedRange, Columns("A:D"))
        .Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
        DeleteCells .Cells, "C", "D"
        DeleteCells .Cells, "D", "C"
    End With
End Sub

Sub DeleteCells(r As Range, s1 As String, s2 As String)
    Dim rDel        As Range
    Dim iRow        As Long
    Dim iOfs        As Long
    
    For iRow = 1 To r.Rows.Count
        With r(iRow, "A")
            If .Offset(, 3).Value = s1 Then

                iOfs = 1
                Do
                    If .Offset(iOfs).Value = .Value Then
                        If .Offset(iOfs, 3).Value = s2 Then
                            
                            If rDel Is Nothing Then
                                Set rDel = Union(.EntireRow, .Offset(iOfs).EntireRow)
                            Else
                                If Intersect(rDel, Rows(iRow)) Is Nothing Then
                                    Set rDel = Union(rDel, .EntireRow, .Offset(iOfs).EntireRow)
                                End If
                            End If
                            Exit Do
                        
                        Else
                            iOfs = iOfs + 1
                        End If
                    
                    Else
                        Exit Do
                    End If
                Loop
            End If
        End With
    Next iRow

    If Not rDel Is Nothing Then rDel.Delete
End Sub

That gets your 2914 rows down to 1364.

Forgive me, I don't have the time to address offsetting transactions that are sums of other transactions.