Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to find all possible sums of two numbers in a list in Excel?

Supposing here is a list of numbers, now could you find all possible sums of two numbers in this list as below screenshot shown? In Excel, there is no built-in function can solve this job. Now I introduce you a VBA code to handle it.
doc all possible sums of two numbers 1

Find all possible sums of two numbers in a list with VBA code

Find all possible combinations of two lists with List All Combinationsgood idea3


List all combinations of two or more lists in Excel sheet

For example, you have two list of values, what you want is to combine these two lists to get all possible combinations as below screenshot shown. In general, you can combine them one by one, but if there are dozens of values needed to combine, this manual method cost much time. In this case, you can try to apply Kutools for Excel's List All Combinations utility, which can quickly generate all combinations of two or more lists you need..  Click for 60 days free trial!
doc list combinations
 
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days.

Find all possible sums of two numbers in a list with VBA code

If you want to find all possible sums of two numbers in a list, you can apply below VBA code.

1. Press Alt + F11 keys to display the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, copy and paste below code to the module.

VBA: List all possible sums of two numbers in a list

Sub Combinations()
'UpdatebyExtendoffice20180628
    Dim xRgArr
    Dim xNum As Long
    Dim I, J, K As Long
    Dim xTemp As Double
    Dim xRg As Range
    Dim xRgCount As Long
    Dim xDic As New Dictionary
    On Error Resume Next
    Set xRg = Application.InputBox("Select a list (one column):", "KuTools for Excel", Selection.Address, , , , , 8)
    If (xRg Is Nothing) Or (xRg.Count = 1) Then Exit Sub
    xRgCount = xRg.Count
    K = 1
    ReDim xRgArr(1 To xRgCount)
    For Each xCell In xRg
      xRgArr(K) = xCell.Value
      K = K + 1
    Next
    K = 0
    For I = 1 To xRgCount
        For J = I + 1 To xRgCount
            xTemp = xRgArr(I) + xRgArr(J)
            If Not xDic.Exists(xTemp) Then
                xDic.Add CDbl(xTemp), CStr(xTemp)
                K = K + 1
            End If
        Next
    Next
    Range("C1").Resize(xDic.Count, 1) = Application.WorksheetFunction.Transpose(xDic.Keys)
End Sub

doc all possible sums of two numbers 2

3. Click Tools > References, in the popping dialog, check Microsoft Scripting Runtime checkbox.

doc all possible sums of two numbers 3 doc arrow right doc all possible sums of two numbers 4

4. Click OK to close the dialog, then press F5 to run the code, a dialog pops out to remind you selecting a list to find the possible sums.
doc all possible sums of two numbers 5

5. Click OK, then all possible sums of two numbers in the list are listed in column C.
doc all possible sums of two numbers 6

Tip:in the code string, Range("C1").Resize(xDic.Count, 1), you can change C1 to another cell as you need to place the result.


Find all possible combinations of two lists with List All Combinations

If you want to find or list all possible combinations of two lists as below screenshot shown, the List All Combinations utility of Kutools for Excel can do you a favor.
doc all possible sums of two numbers 7

1. Click Kutools > Insert > List All Combinations.
doc all possible sums of two numbers 8

2. In the List All Combinations dialog, click the select button to select the first list you will combine.
doc all possible sums of two numbers 9

3. Click Add to add the list into Combinations list section.
doc all possible sums of two numbers 10

4. Repeat step 2 and 3 to add the second list to the Combinations list.
doc all possible sums of two numbers 11

5. Then click Ok to select a cell to place the combination result.
doc all possible sums of two numbers 12

6. Click OK. All possible combinations have been listed in cells.
doc all possible sums of two numbers 13

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.