How to Return Dynamic Array of Each Sum for 3 or more combinations of numbers?

[user47]

Hello,

Have not seen this posted before so I hope it is not a stupid question.

If I have a column with numbers and I wanted to return the sum of combinations of every 2 numbers I can do the following:

Book1
	A	B	C	D	E	F	G	H
3		11977	6562	1679	6642	6623	3953	5755
4	11977	23954	18539	13656	18619	18600	15930	17732
5	6562	18539	13124	8241	13204	13185	10515	12317
6	1679	13656	8241	3358	8321	8302	5632	7434
7	6642	18619	13204	8321	13284	13265	10595	12397
8	6623	18600	13185	8302	13265	13246	10576	12378
9	3953	15930	10515	5632	10595	10576	7906	9708
10	5755	17732	12317	7434	12397	12378	9708	11510
Sheet1

Cell Formulas
Range	Formula
B4:H10	B4	=$A$4:$A$10+TRANSPOSE($A$4:$A$10)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B7:AT8,B9:K15,R9:AT15,M16:Q22,F46:AT46,AK16:AT45,B4:L6,R4:AT6,M47:AT48	Cell Value	=$A$2	text	NO
B7:AT8,B9:K15,R9:AT15,M16:Q22,F46:AT46,AK16:AT45,B4:L6,R4:AT6,M47:AT48	Cell Value	duplicates	text	NO

Of course these have duplicates off either side of the diagonal- I would just ignore one side and can analyze the other (haven't figured out a way to return only unique values in the table).
But what if I wanted to take it a step further and return the sums for every unique combination of sets of three numbers instead? I can't figure out how to do this in excel except manually which is very time consuming and prone to mistakes. Is this possible? This is just an example but my actual dataset would be about 150 numbers and I need to add them up in 3s or even 4s or 5s. Is there a general formula I can use to do this?

Thanks for any help/ ideas- appreciated as usual!!

 

[bebo021999]

I'd offer a VBA solution.
There are two files: one for sequenceNumber (for ex: from 0...10)

SequenceNumbers.xlsm

drive.google.com

another for RadomNumber (for ex: 15,35,600,20,....)

ListNumbers.xlsm

drive.google.com

In each file, the are two buttons: for dupplicate combination and for non dupplicate combination
pick one that most suit.
1 example VBA code:

Option Explicit
Sub combinationNoDup()
Dim i1 As Long, i2 As Long, i3 As Long, k As Long
Dim num1 As Double, num2 As Double, sum As Double
Dim res(1 To 1000000, 1 To 4)
num1 = Range("B2").Value
num2 = Range("B3").Value
If num2 <= num1 Then
    MsgBox "number 2 must be larger then number 1"
    Exit Sub
End If
For i1 = num1 To num2 - 2
    For i2 = i1 + 1 To num2 - 1
        For i3 = i2 + 1 To num2
            k = k + 1
            res(k, 1) = i1
            res(k, 2) = i2
            res(k, 3) = i3
            res(k, 4) = i1 + i2 + i3
        Next
    Next
Next
With Range("D2")
    .Resize(1000000, 4).ClearContents
    .Resize(k, 4).Value = res
End With
End Sub

 

[KolGuyXcel]

The formula used is simple. In cell B2, the formula is =IF(OR($A2=B$1,B$1=$L1,$L1=$A1),"",$A2+B$1+$L2). This is not dynamic as Column L would has to be shifted accordingly for other sets of numbers.
I don't think my table is the "solution", par se. I'm still trying to understand the requirement, mostly how to represent.
The only "logic" here is that a "shift" in reading the original set of numbers would let us "reach" the unique combinations a little easier with lesser "looping".

Also, will a macro solution be acceptable?

 

[Georgiboy]

Something like the below?

Book1
	A	B	C	D
1
2	1		1+2+3=	6
3	2		1+2+4=	7
4	3		1+3+4=	8
5	4		2+3+4=	9
6	5		1+2+5=	8
7	6		1+3+5=	9
8	7		2+3+5=	10
9			1+4+5=	10
10			2+4+5=	11
11			3+4+5=	12
12			1+2+6=	9
13			1+3+6=	10
14			2+3+6=	11
15			1+4+6=	11
16			2+4+6=	12
17			3+4+6=	13
18			1+5+6=	12
19			2+5+6=	13
20			3+5+6=	14
21			4+5+6=	15
22			1+2+7=	10
23			1+3+7=	11
24			2+3+7=	12
25			1+4+7=	12
26			2+4+7=	13
27			3+4+7=	14
28			1+5+7=	13
29			2+5+7=	14
30			3+5+7=	15
31			4+5+7=	16
32			1+6+7=	14
33			2+6+7=	15
34			3+6+7=	16
35			4+6+7=	17
36			5+6+7=	18
Sheet2

Cell Formulas
Range	Formula
C2:D36	C2	=LET(y, COUNTA(A2:A8), x, MOD(INT((SEQUENCE(2^y)-1)/2^SEQUENCE(, y, 0)), 2),a, IF(FILTER(x, MMULT(x, SEQUENCE(y)^0)=3), TRANSPOSE(A2:A8), ""),HSTACK(BYROW(a,LAMBDA(x,TEXTJOIN("+",TRUE,x))) & "=",BYROW(a,LAMBDA(x,SUM(x)))))
Dynamic array formulas.

 

[user47]

I'd offer a VBA solution.
There are two files: one for sequenceNumber (for ex: from 0...10)

SequenceNumbers.xlsm

drive.google.com

another for RadomNumber (for ex: 15,35,600,20,....)

ListNumbers.xlsm

drive.google.com

In each file, the are two buttons: for dupplicate combination and for non dupplicate combination
pick one that most suit.
1 example VBA code:

Option Explicit
Sub combinationNoDup()
Dim i1 As Long, i2 As Long, i3 As Long, k As Long
Dim num1 As Double, num2 As Double, sum As Double
Dim res(1 To 1000000, 1 To 4)
num1 = Range("B2").Value
num2 = Range("B3").Value
If num2 <= num1 Then
    MsgBox "number 2 must be larger then number 1"
    Exit Sub
End If
For i1 = num1 To num2 - 2
    For i2 = i1 + 1 To num2 - 1
        For i3 = i2 + 1 To num2
            k = k + 1
            res(k, 1) = i1
            res(k, 2) = i2
            res(k, 3) = i3
            res(k, 4) = i1 + i2 + i3
        Next
    Next
Next
With Range("D2")
    .Resize(1000000, 4).ClearContents
    .Resize(k, 4).Value = res
End With
End Sub

Whoa!
This is perfect!!

I know in my title I asked for a dynamic array but this works absolutely perfectly (although it would be interesting to see if a dynamic array could even be done).
The one with the list is what will work best for my use case.

Question: is there a way to adjust this for cases of 4 or 5 combinations? (I'm not that VBA savvy but would like to dive into your code and learn this super power!)

Thank you so much!!

 

[user47]

Something like the below?

Book1
	A	B	C	D
1
2	1		1+2+3=	6
3	2		1+2+4=	7
4	3		1+3+4=	8
5	4		2+3+4=	9
6	5		1+2+5=	8
7	6		1+3+5=	9
8	7		2+3+5=	10
9			1+4+5=	10
10			2+4+5=	11
11			3+4+5=	12
12			1+2+6=	9
13			1+3+6=	10
14			2+3+6=	11
15			1+4+6=	11
16			2+4+6=	12
17			3+4+6=	13
18			1+5+6=	12
19			2+5+6=	13
20			3+5+6=	14
21			4+5+6=	15
22			1+2+7=	10
23			1+3+7=	11
24			2+3+7=	12
25			1+4+7=	12
26			2+4+7=	13
27			3+4+7=	14
28			1+5+7=	13
29			2+5+7=	14
30			3+5+7=	15
31			4+5+7=	16
32			1+6+7=	14
33			2+6+7=	15
34			3+6+7=	16
35			4+6+7=	17
36			5+6+7=	18
Sheet2

Cell Formulas
Range	Formula
C2:D36	C2	=LET(y, COUNTA(A2:A8), x, MOD(INT((SEQUENCE(2^y)-1)/2^SEQUENCE(, y, 0)), 2),a, IF(FILTER(x, MMULT(x, SEQUENCE(y)^0)=3), TRANSPOSE(A2:A8), ""),HSTACK(BYROW(a,LAMBDA(x,TEXTJOIN("+",TRUE,x))) & "=",BYROW(a,LAMBDA(x,SUM(x)))))
Dynamic array formulas.

WOWOWOWOWOWOWOWWWWW!!! You did it!!!

Yes!! This absolutely works as well and i guess it's better than the "table" I was trying to think about.
Can this formula be edited to work for cases with up to 5 combinations and say a list of 50-80 numbers (I've figure out this latter part expanding the reference in col A)?

Thanks so much!!!
You guys are so smart and awesome!!

 

[bebo021999]

Whoa!
This is perfect!!

I know in my title I asked for a dynamic array but this works absolutely perfectly (although it would be interesting to see if a dynamic array could even be done).
The one with the list is what will work best for my use case.

Question: is there a way to adjust this for cases of 4 or 5 combinations? (I'm not that VBA savvy but would like to dive into your code and learn this super power!)

Thank you so much!!

In previous code, for 3 combinations, there are i1, i2, i3 variables and 3 for...loop
below code is for 5 combination, with i1,i2,i3,i4,i5 variables and 5 for...loop

Option Explicit
Sub combinationNoDup()
Dim lr&, rng, i1 As Double, i2 As Double, i3 As Double, i4 As Double, i5 As Double, k As Double
Dim sum As Double
lr = Cells(Rows.Count, "B").End(xlUp).Row
rng = Range("B2:B" & lr).Value
Dim res(1 To 1000000, 1 To 6)
For i1 = 1 To UBound(rng)
    For i2 = i1 + 1 To UBound(rng)
        For i3 = i2 + 1 To UBound(rng)
            For i4 = i3 + 1 To UBound(rng)
                For i5 = i4 + 1 To UBound(rng)
                    k = k + 1
                    res(k, 1) = rng(i1, 1)
                    res(k, 2) = rng(i2, 1)
                    res(k, 3) = rng(i3, 1)
                    res(k, 4) = rng(i4, 1)
                    res(k, 5) = rng(i5, 1)
                    res(k, 6) = res(k, 1) + res(k, 2) + res(k, 3) + res(k, 4) + res(k, 5)
                Next
            Next
        Next
    Next
Next
With Range("D2")
    .Resize(1000000, 6).ClearContents
    .Resize(k, 6).Value = res
End With
End Sub

 

[user47]

The formula used is simple. In cell B2, the formula is =IF(OR($A2=B$1,B$1=$L1,$L1=$A1),"",$A2+B$1+$L2). This is not dynamic as Column L would has to be shifted accordingly for other sets of numbers.
I don't think my table is the "solution", par se. I'm still trying to understand the requirement, mostly how to represent.
The only "logic" here is that a "shift" in reading the original set of numbers would let us "reach" the unique combinations a little easier with lesser "looping".

Also, will a macro solution be acceptable?

Hello,

Yes a macro will be acceptable as well.
I am beginning to think a table can only work for showing 2 way combinations like a grid (row x col )- but maybe cannot exist for 3 way or higher?

User Georgiboy and user bebo021999 proposed ideal solutions with dynamic arrays and with VBA respectively- but the "lookup" table is a list which seems to actually be the best presentation of the results. I can sort it based on the sum and then lookup by sum which 3 numbers added up to this value.

Thanks so much for your help!!

I appreciate all of you!

 

[Georgiboy]

I would recomend using @bebo021999's method, my formula does not like more than 20 numbers being fed to it i suppose due to the amount combinations and my use of the LET function. Below is an example for reference of changing the amount of combo's and setting a target for the result:

Book1
	A	B	C	D	E	F	G
1
2	1		2+3+4+6+7+8=	30		Combo's	6
3	2		1+3+5+6+7+8=	30		Target	30
4	3		2+3+4+5+7+9=	30
5	4		1+3+4+6+7+9=	30
6	5		1+2+5+6+7+9=	30
7	6		1+3+4+5+8+9=	30
8	7		1+2+4+6+8+9=	30
9	8		1+2+3+7+8+9=	30
10	9		2+3+4+5+6+10=	30
11	10		1+3+4+5+7+10=	30
12	11		1+2+4+6+7+10=	30
13	12		1+2+4+5+8+10=	30
14	13		1+2+3+6+8+10=	30
15	14		1+2+3+5+9+10=	30
16	15		1+3+4+5+6+11=	30
17	16		1+2+4+5+7+11=	30
18	17		1+2+3+6+7+11=	30
19	18		1+2+3+5+8+11=	30
20	19		1+2+3+4+9+11=	30
21	20		1+2+4+5+6+12=	30
22			1+2+3+5+7+12=	30
23			1+2+3+4+8+12=	30
24			1+2+3+5+6+13=	30
25			1+2+3+4+7+13=	30
26			1+2+3+4+6+14=	30
27			1+2+3+4+5+15=	30
Sheet2

Cell Formulas
Range	Formula
C2:D27	C2	=LET(y, COUNTA(A2:A21), x, MOD(INT((SEQUENCE(2^y)-1)/2^SEQUENCE(, y, 0)), 2), a, IF(FILTER(x, MMULT(x, SEQUENCE(y)^0)=$G$2), TRANSPOSE(A2:A21), ""), r,HSTACK(BYROW(a,LAMBDA(x,TEXTJOIN("+",TRUE,x))) & "=",BYROW(a,LAMBDA(x,SUM(x)))), FILTER(r,INDEX(r,,2)=$G$3))
Dynamic array formulas.

Maybe someone else has a better formula (I can learn then)

 

[fjns]

Hi, see the linked file for a possible solution...
Only cells A1 and B1 need to be filled with positive integers. All other cells are filled with formulas.

The formulas used in the table:
C1: =LET(z,$A1+COLUMN()-1,IF(z>$B1,"",z)) (Range: C1:J1 ...)
A2: =LET(d,A$1-1,i,A1-d,c,B$1-A$1+1,IF(ROW()>(c-1)*(c-2)/2+1,"",IF(ROW()<i*(c-2)-i*(i-1)/2+2,i+d,i+d+1))) (Range: A2:A37 ...)
B2: =IF(A2="","",A2+1)
B3: =LET(c,B$1-A$1+1,IF(ROW()>(c-1)*(c-2)/2+1,"",IF(A3=A2,B2+1,A3+1))) (Range: B3:B37 ...)
C2: =IF(C$1="","",IF(C$1>$B2,$A2+$B2+C$1,"")) (Range: C2:J37 ...)

Combinations3.xlsx