Conditional sum

[rize1159]

I have some data in A1:A50 and I want to combine 4 cells data by certain sum condition? what will be the possible code. e.g

1,2,3,4,5,6,7,8,9 ......

and I want to combine those 4 which give a sum of 8, then result shoud be 1,2,2,3.
Can any body give some code.

[Bernie Deitrick]

Since 2 only appears once in your list, and you want a solution that has it twice... are there limits on re-use of data? Why not 2,2,2,2? Does it have to be four cells? Do you have 50 cells? Or more? Or fewer?

You could try brute force, assuming that numbers cannot be used twice...

Please Login or Register  to view this content.

[rize1159]

thank you Bernie.
Following are answers to your questions.

1. I have taken 2 as example. it can be three but there is limit on reuse of data. only one cell can be reused and that is also as 2,2 or 3,3 i.e two times.
2. 2,2,2,2 is not required as one cell can be used only two times.
3. No, exactly saying I want five cells. 4 individual and 5th as repetitive.
4. I dont have 50, it varies. from 14 to 28.
5. Results can't be achieved if I dont reuse data.

[rize1159]

code you have given creates everything except combination for 8.

[Bernie Deitrick]

How about these versions - requires that one number be used twice...though i think it is impossible to get 8 from the sum of 3 unique and 1 doubled number (1 + 1 + 2 + 3 + 4 = 11 would be the lowest possible sum) is the requirement for 5 numbers (the first version) or just for 4 numbers - 2 unique and one doubled (the second version)?

Please Login or Register  to view this content.

[rize1159]

Bernie you are great but there is a problem. Code is generating combination for sum of 40 based on its own calculation and not looking on the data given in A1:A14. I want to code to look into data and then generate combination based on data.

[rize1159]

Book1.xlsmmy real data is attached in the book and i want to generate combinations for sum of 40.

[Bernie Deitrick]

Sorry - I'm a bonehead. I though you had the numbers 1 to whatever instead of random numbers.

Sub TestMacro5numbersV2()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim c As Integer
Dim iTar As Integer

iTar = 40 'Value you need to sum to....

c = Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To c - 3
For j = i + 1 To c - 2
For k = j + 1 To c - 1
For l = k + 1 To c
If 2 * Cells(i, 1) + Cells(j, 1) + Cells(k, 1) + Cells(l, 1) = iTar Then
MsgBox Cells(i, 1) & " + " & Cells(i, 1) & " + " & Cells(j, 1) & " + " & Cells(k, 1) & " + " & Cells(l, 1)
End If
If Cells(i, 1) + 2 * Cells(j, 1) + Cells(k, 1) + Cells(l, 1) = iTar Then
MsgBox Cells(i, 1) & " + " & Cells(j, 1) & " + " & Cells(j, 1) & " + " & Cells(k, 1) & " + " & Cells(l, 1)
End If
If Cells(i, 1) + Cells(j, 1) + 2 * Cells(k, 1) + Cells(l, 1) = iTar Then
MsgBox Cells(i, 1) & " + " & Cells(j, 1) & " + " & Cells(k, 1) & " + " & Cells(k, 1) & " + " & Cells(l, 1)
End If
If Cells(i, 1) + Cells(j, 1) + Cells(k, 1) + 2 * Cells(l, 1) = iTar Then
MsgBox Cells(i, 1) & " + " & Cells(j, 1) & " + " & Cells(k, 1) & " + " & Cells(l, 1) & " + " & Cells(l, 1)
End If
Next l
Next k
Next j
Next i

End Sub

[rize1159]

Thank you so very much. You have done a great job

[StephenR]

Why have you started two threads on the same topic?