How To Generate All Possible Combinations Of Items From One List

[edebe]

Hi to everybody,

I do have a problem for which I would like to ask your help. I do have a list with decimal number values and I need to calculate all the possible combinations without repetitions.

As you can see in the attached image, I need to calculate not simply 1-element-based combinations, but I need also to calculate all the combinations for 2,3,4,5,6,7,8,9,10-based element combinations.

I already used the combin function to understand how many combinations I will have. Considering that I have 11 elements, the total of the possible combinations are 2047 (no repetitions):

Considering that I'm a rookie, I would like to ask you how can I make this operation without loosing too much time. Thank you in Advance for your help!

 

[Anthony47]

Can you tell me in which two coordinates that value appears?

 

[edebe]

It appears starting from BZU14 to BZU15. Basically, BZU14 is the last value of the first row results, and BZU15 is the last value of the second result row

 

[Anthony47]

To avoid the confusion, use 10000 in A4.
The results are on Row 14; row 15 just reminds which target value we was searching; row 16 measures the difference between the found value (row 14) and the target value (row 15=A4)
Row 15 and 16 are there because that work was intended to search for the combinations that matched a certain target value with a given tolerance; so at the end of the work the original macro was sorting the results for the lowest difference. To cope with your task I just removed the final Sorting, and suggested "to play" with the tolerance to get any of the results.

So just ignore what is on row 15 and 16 (or whichever are the last and the one-before-the-last rows, if you use more or lesst then 11 entries)

Bye

 

[edebe]

To avoid the confusion, use 10000 in A4.
The results are on Row 14; row 15 just reminds which target value we was searching; row 16 measures the difference between the found value (row 14) and the target value (row 15=A4)
Row 15 and 16 are there because that work was intended to search for the combinations that matched a certain target value with a given tolerance; so at the end of the work the original macro was sorting the results for the lowest difference. To cope with your task I just removed the final Sorting, and suggested "to play" with the tolerance to get any of the results.

So just ignore what is on row 15 and 16 (or whichever are the last and the one-before-the-last rows, if you use more or lesst then 11 entries)

Bye

Yes, that's right. The sum of the values in row 14 is 2047 as it's supposed to be. I checked also the first and the last values and they match

 

[edebe]

Try using the workbook you can download here: CercaCombinaz_V1-308MrEx.xlsm

Put your data in sheet LAVORO, in B2 and below
Put in A4 the sum of your data
Put in A1 the value of A4+1
Press the button "AVVIA"

A form will ask you to set a timeout (default is 60 secs), then press "CEERCA" (search)
This is a "brute force" af all the combination that create the sum set in A4 with the tolerance set in A1; given how we populated A4 and A1 it will return all the combinations

At the end, the several columns from C and rightward will say which values (those tagged "1") give the result written in row 14 (with 11 entries); row 15 remind what we searched, row 16 remind wich is the error. The original work had a final Sort (removed here) to first represent the combination with the least error.
If you select a X in row 1then the matched items will be highlighted in column B

If the timeout expires before the search be completed then the final msgbox will say "Stop due to Time Out xx Secs)

Try...

Hey Sorry if I bother you again, today I was keeping using the workbook and it was fine until I got this set of values to calculate:

So, the highest value, which will be also the last one calculated in the workbook, is 2477.82. However, when it ends the operations it appears that many combinations are missing:

How can I solve this? I just followed the instructions and I cannot understand where the problem is.

 

[Anthony47]

Excel has 16384 columns; how many combinations do you expect from 11 elements? Your results will probably be truncated al column XEV, ie after 16376 results

 

[edebe]

Excel has 16384 columns; how many combinations do you expect from 11 elements? Your results will probably be truncated al column XEV, ie after 16376 results

Do you think there could be a solution to this problem?

Now I'm trying running the workbook without timing.It seems there are 131071 combinations to be calculated. It will take some time, but worth a try maybe.

 

[Akuini]

Hi, @edebe

I found a code by MickG in this thread

all combinations without repeats

Hi, I am unsure if this already exists but have spent days looking for what I need and cannot find it. What I am looking for is a VBA that will provide all possible unique combinations within a range. The range will be variable to input - in column A from 1 to 12 entries. Lets say- A1 -...

www.mrexcel.com

Note: to make the code readable you can convert it via this site: https://www.browserling.com/tools/bbcode-to-text
I amend the code to suit your requirement.
For input size 17 and output size: 131071 it took about 4 sec.

input size: 17
output size: 131071
It's done in: 3,625 seconds


You need to put the number in col A, starting at A1 then run Sub Combinations_1()


The code:

Option Explicit
Dim va
Dim z As Long
Sub Combinations_1()
'Ref PCG
Dim rRng As Range, p
Dim a As Long, i As Long, x As Long
Dim vElements, lRow As Long, vresult As Variant
Dim t
t = Timer
 'you need to put the number in col A, starting at A1
 
 Set rRng = Range("A1", Range("A1").End(xlDown))
 a = rRng.Cells.Count

For i = 1 To a
    x = x + WorksheetFunction.Combin(a, i)
Next

Debug.Print "input size: " & a
Debug.Print "output size: " & x

ReDim va(1 To x, 1 To a + 1)
z = a + 1

vElements = Application.Index(Application.Transpose(rRng), 1, 0)

For i = 1 To a
ReDim vresult(1 To i)
Call CombinationsNP_1(vElements, CInt(i), vresult, lRow, 1, 1)
Next

Range("C1").Resize(UBound(va, 1), z) = va

Debug.Print "It's done in: " & Timer - t & " seconds"
End Sub
 
Sub CombinationsNP_1(vElements As Variant, p As Long, vresult As Variant, lRow As Long, iElement As Long, iIndex As Long)
Dim i As Long, j As Long
For i = iElement To UBound(vElements)
 vresult(iIndex) = vElements(i)
 If iIndex = p Then
 lRow = lRow + 1
 
 For j = 1 To p
    va(lRow, j) = vresult(j)
    va(lRow, z) = va(lRow, z) + va(lRow, j)
 Next
 Else
 Call CombinationsNP_1(vElements, p, vresult, lRow, i + 1, iIndex + 1)
 End If
Next i
End Sub

 

[edebe]

Hi, @edebe

I found a code by MickG in this thread

all combinations without repeats

Hi, I am unsure if this already exists but have spent days looking for what I need and cannot find it. What I am looking for is a VBA that will provide all possible unique combinations within a range. The range will be variable to input - in column A from 1 to 12 entries. Lets say- A1 -...

www.mrexcel.com

Note: to make the code readable you can convert it via this site: https://www.browserling.com/tools/bbcode-to-text
I amend the code to suit your requirement.
For input size 17 and output size: 131071 it took about 4 sec.

input size: 17
output size: 131071
It's done in: 3,625 seconds


You need to put the number in col A, starting at A1 then run Sub Combinations_1()


The code:

Option Explicit
Dim va
Dim z As Long
Sub Combinations_1()
'Ref PCG
Dim rRng As Range, p
Dim a As Long, i As Long, x As Long
Dim vElements, lRow As Long, vresult As Variant
Dim t
t = Timer
 'you need to put the number in col A, starting at A1
 
 Set rRng = Range("A1", Range("A1").End(xlDown))
 a = rRng.Cells.Count

For i = 1 To a
    x = x + WorksheetFunction.Combin(a, i)
Next

Debug.Print "input size: " & a
Debug.Print "output size: " & x

ReDim va(1 To x, 1 To a + 1)
z = a + 1

vElements = Application.Index(Application.Transpose(rRng), 1, 0)

For i = 1 To a
ReDim vresult(1 To i)
Call CombinationsNP_1(vElements, CInt(i), vresult, lRow, 1, 1)
Next

Range("C1").Resize(UBound(va, 1), z) = va

Debug.Print "It's done in: " & Timer - t & " seconds"
End Sub
 
Sub CombinationsNP_1(vElements As Variant, p As Long, vresult As Variant, lRow As Long, iElement As Long, iIndex As Long)
Dim i As Long, j As Long
For i = iElement To UBound(vElements)
 vresult(iIndex) = vElements(i)
 If iIndex = p Then
 lRow = lRow + 1
 
 For j = 1 To p
    va(lRow, j) = vresult(j)
    va(lRow, z) = va(lRow, z) + va(lRow, j)
 Next
 Else
 Call CombinationsNP_1(vElements, p, vresult, lRow, i + 1, iIndex + 1)
 End If
Next i
End Sub

Hi @Akuini , thanks for the reply! The example in thread you mentioned, seems that needed to find the possible combinations only. In my case, I guess, there a step more: I need to calculate the addition of all the components of a given combination.

Is that to complicate things?

 

[Akuini]

Did you try it?
The last column is the sum of the combination