Get the combination from values in VBA

[mehidy1437]

Hello guys

I have value in M5:U5

25

10

10

20

18

12

10

5

19

Now i want to get the combination of value which sum will be 30
Some values may not have any combination, those values will go to the last row, value like 20 & 19

Like as below:

25							5
	10	10				10
				18	12
			20					19

Please help me to solve this.

Sorry my XL2BB is not working at this laptop. So I can't post the data with that.

 

[JGordon11]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	25	10	10	20	18	12	10	5	19		30
2											Sum
3	0	0	0	0	18	12	0	0	0		30
4	0	0	0	20	0	0	10	0	0		30
5	0	0	10	20	0	0	0	0	0		30
6	0	10	0	20	0	0	0	0	0		30
7	0	10	10	0	0	0	10	0	0		30
8	25	0	0	0	0	0	0	5	0		30
9
Sheet2

Cell Formulas
Range	Formula
A3:I8	A3	=LET(a,MID(DEC2BIN(SEQUENCE(512,,0),9), SEQUENCE(1,9),1)*A1:I1,b, MMULT(a,SEQUENCE(9,,,0)), FILTER(a,b=K1))
K3:K8	K3	=BYROW(A3#,LAMBDA(x,SUM(x)))
Dynamic array formulas.

 

[mehidy1437]

This is totally wrong, I have specified the desired output.
Also I need the solution with vba.

 

[mehidy1437]

I'm stuck with this. Can anyone help me figure it out, please?

 

[Ezguy4u]

mehidy Let's get the ball rolling. Now your problem has more questions than answers. But we have to start somewhere. This is just the beginning version. If you like what you see than we will continue.

Sub Prog()

Dim amt1 As Integer
Dim row1 As Integer
Dim col1 As Integer
Dim colcnt As Integer
Dim colcnt2 As Integer

row1 = 2
col1 = 1
colcnt = 1
colcnt2 = Cells(1, 1).End(xlToRight).Column
col1 = colcnt2 + 2

Range("A1").Cut Destination:=Range("A2")

For i = 1 To 8

Cells(row1, 11) = "=sum(r[0]c[-10]:r[0]c[-2])"

For a = 1 To 8

If Cells(1, colcnt + 1) = "" Then
colcnt = Cells(1, colcnt + 1).End(xlToRight).Column
colcnt = colcnt - 1
End If
Cells(1, colcnt + 1).Select

amt1 = Cells(1, colcnt + 1) + Cells(row1, 11)

If amt1 <= Range("K1") Then

Cells(1, colcnt + 1).Cut Destination:=Cells(row1, colcnt + 1)
colcnt = colcnt + 1

If amt1 = 30 Then Exit For

Else

colcnt = colcnt + 1
If colcnt2 = colcnt Then Exit For

End If

Next a

colcnt = Cells(1, 1).End(xlToRight).Column
If colcnt = col1 Then Exit Sub

Cells(1, colcnt).Cut Destination:=Cells(row1 + 1, colcnt)
If colcnt2 = colcnt Or colcnt = col1 Then Exit Sub

row1 = row1 + 1

Next i

End Sub

23-07-21 work 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	25	10	10	5	18	12	10	11	19		30
2
3
4
5
6
7
8
9
10	25	10	10	20	18	12	10	5	19
11	25	10	10	5	18	12	10	11	19
Data