Find any nth values from list that can sumup to a value not greater than that cell

[shersalafi]

Hi, to every one.
I am creating a report. i have a list of different numbers in range M4:M21
What i need is to sum up any 3 or 4 values that can near to 240 but not more than that
For Example I have This List
Range M4 to M21

57
81
87
50
85
66
70
72
81
54
82
59
86
53
69
84
63
83

Now i need Any Combination of 3 values or 4 values which sums the maximum number but should be less than 240.
Like in Above List 86 + 84 + 69 = 239
Then 63 + 82 + 87 = 232
It should give me Like That but not more than 240
and the cells which are used should not be used again like Cell M16 (i.e. 86) , M19, M18 is used in one combination so it should not be used again.
And 3 or four values which is used should be coloured differently.
if there is any macro you can suggest
Thanks for you support in advance.

 

[Saurabhj]

Hi,

I created VBA to generate 5 combinations.

Please check.

1. All numbers are in column A and sorted from Highest to Lowest.
2. Column B contains "N" for each number to show that the number is used or not.
3. Column F, G, H and I contains the combination and the sum.

Sub createCombination()
Dim num1 As Integer, num2 As Integer, num3 As Integer
Dim n1 As Integer, n2 As Integer, n3 As Integer
Dim totalNum As Integer, rownum As Integer, total As Integer
totalNum = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Const sumReqd As Integer = 240

Application.ScreenUpdating = False
Restart:
For n1 = 1 To totalNum
    If Range("B" & n1) = "N" Then
        num1 = Range("A" & n1)
        For n2 = n1 + 1 To totalNum
            If Range("B" & n2) = "N" Then
                num2 = Range("A" & n2)
                For n3 = n2 + 1 To totalNum
                    If Range("B" & n3) = "N" Then
                        num3 = Range("A" & n3)
                        total = num1 + num2 + num3
                        If total < sumReqd Then
                            Range("B" & n1) = "Y"
                            Range("B" & n2) = "Y"
                            Range("B" & n3) = "Y"
                            rownum = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row + 1
                            Range("F" & rownum) = num1
                            Range("G" & rownum) = num2
                            Range("H" & rownum) = num3
                            Range("I" & rownum) = total
                            GoTo Restart
                        End If
                    End If
                Next
            End If
        Next
    End If
Next
Application.ScreenUpdating = True
End Sub

NumberCombinations.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	87	Y				num1	num2	num3	total
2	86	Y				87	86	66	239
3	85	Y				85	84	70	239
4	84	Y				83	82	72	237
5	83	Y				81	81	69	231
6	82	Y				63	59	57	179
7	81	Y				54	53	50	157
8	81	Y
9	72	Y
10	70	Y
11	69	Y
12	66	Y
13	63	Y
14	59	Y
15	57	Y
16	54	Y
17	53	Y
18	50	Y
19
20
21
22
23
24
25
26
Sheet1

 

[shersalafi]

Hi,

I created VBA to generate 5 combinations.

Please check.

1. All numbers are in column A and sorted from Highest to Lowest.
2. Column B contains "N" for each number to show that the number is used or not.
3. Column F, G, H and I contains the combination and the sum.

Sub createCombination()
Dim num1 As Integer, num2 As Integer, num3 As Integer
Dim n1 As Integer, n2 As Integer, n3 As Integer
Dim totalNum As Integer, rownum As Integer, total As Integer
totalNum = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Const sumReqd As Integer = 240

Application.ScreenUpdating = False
Restart:
For n1 = 1 To totalNum
    If Range("B" & n1) = "N" Then
        num1 = Range("A" & n1)
        For n2 = n1 + 1 To totalNum
            If Range("B" & n2) = "N" Then
                num2 = Range("A" & n2)
                For n3 = n2 + 1 To totalNum
                    If Range("B" & n3) = "N" Then
                        num3 = Range("A" & n3)
                        total = num1 + num2 + num3
                        If total < sumReqd Then
                            Range("B" & n1) = "Y"
                            Range("B" & n2) = "Y"
                            Range("B" & n3) = "Y"
                            rownum = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row + 1
                            Range("F" & rownum) = num1
                            Range("G" & rownum) = num2
                            Range("H" & rownum) = num3
                            Range("I" & rownum) = total
                            GoTo Restart
                        End If
                    End If
                Next
            End If
        Next
    End If
Next
Application.ScreenUpdating = True
End Sub

NumberCombinations.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	87	Y				num1	num2	num3	total
2	86	Y				87	86	66	239
3	85	Y				85	84	70	239
4	84	Y				83	82	72	237
5	83	Y				81	81	69	231
6	82	Y				63	59	57	179
7	81	Y				54	53	50	157
8	81	Y
9	72	Y
10	70	Y
11	69	Y
12	66	Y
13	63	Y
14	59	Y
15	57	Y
16	54	Y
17	53	Y
18	50	Y
19
20
21
22
23
24
25
26
Sheet1

Thanks A lot

Saurabhj Thank you so much for your kind support. Its worked for me. now having an issue that i want to cut a piece of wood which length is 240 centimetres so according to your screenshot first Four Totals are good i.e. I2 to I5 in which we have remaining less amount of Scrap so our wood pieces will not be go in trash (less loss in lengths when cutting wood pieces) but i want to add another cell also like See I6 cell, its total is 179 so we can also add F7(value 54) to make it 233. so from 240CM of wood only 7CM will go in the dustbin and 233 will be used. see image below​

 

[shersalafi]

I tried it myself and edit this macro but not working because now it sums up only 4 values and leaves others. I need to make it less than 240 whether it could be by summing two values or three or four.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: How To Find Maximum Sum From The List With nth Number of Values Not Greater Than To X
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[shersalafi]

Sorry it was my first time, Yes I will mention there your link also, and there on that forum link is this one

How To Find Maximum Sum From The List With nth Number of Values Not Greater Than To X [SOLVED]

Cross-posted at https://www.mrexcel.com/board/threads/find-any-nth-values-from-list-that-can-sumup-to-a-value-not-greater-than-that-cell.1191127/ with solution Hi, I am new here. I have a list of different numbers in range A1:A18 What i need is to sum up any 3 or 4 values that can be...

www.excelforum.com