Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Checking for Sum of Values (Excel 2000)

    I have a list of 1000 records. I would like to run a formula to tell me which 2 values will add up to give me 758. Any formula that will do the trick?

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Checking for Sum of Values (Excel 2000)

    VBA code is probably more efficient here, but perhaps one of the array formula gurus will come up with something.
    Problems with a formula:
    - There are 1000*999/2 = 499500 pairs of values to be checked.
    - There can be more than one solution.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking for Sum of Values (Excel 2000)

    Here is a VBA solution that will list all "matching" addresses to the right of the list:

    <pre>Sub FindCombinations()
    Dim oCell As Range
    Dim oFoundcell As Range
    Dim oFirstFound As Range
    Dim lVal As Long
    Dim lSum As Long
    Dim iFoundCount As Integer
    Dim lCalcmode As Long
    lSum = Application.InputBox("Please enter the sum", "Find combinations", , , , , , 1)
    Application.ScreenUpdating = False
    lCalcmode = Application.Calculation
    Application.Calculation = xlCalculationManual
    For Each oCell In Selection
    Set oFirstFound = Nothing
    Set oFoundcell = Nothing
    lVal = lSum - oCell.Value
    iFoundCount = 0
    On Error Resume Next
    Set oFirstFound = Selection.Find(What:=lVal, LookIn:=xlValues, LookAt _
    :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    If Not oFirstFound Is Nothing Then
    Set oFoundcell = oFirstFound
    Do
    On Error GoTo 0
    If Not (oFoundcell Is Nothing) Then
    iFoundCount = iFoundCount + 1
    oCell.Offset(, iFoundCount).Value = oFoundcell.Address
    End If
    Set oFoundcell = Selection.FindNext(after:=oFoundcell)
    Loop Until oFoundcell.Address = oFirstFound.Address Or (oFoundcell Is Nothing)
    End If
    Next
    Application.ScreenUpdating = True
    Application.Calculation = lCalcmode
    End Sub</pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking for Sum of Values (Excel 2000)

    Thanks for VBA Code. Very clever. I would have gone the long way around at match each cell with the other 999 values.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •