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]

May I ask which is your final goal? Creating the combinations seem to me the way you plan to solve another problem...
Bye

 

[edebe]

Hi there, my goal it's just to calculate the value of all the these combinations. Just that.

 

[Anthony47]

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...

 

[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...

Yes, it worked perfectly! Thank you so much for that!

I'm wondering if there's a way to know which elements created the resulting values. It's not really important honestly, but it will provide more information

 

[Anthony47]

I updated the file in the last 5 minutes.
Worksheet "Help" contains some information for the user
The "1" in vertical say which values were accounted for that sum; if you select a X in row 1 then values in column B will highlight

Bye

 

[edebe]

Edit:

I noticed that the great majority of the results don't have decimals, which seems quite strange to be honest giving the original values. Also you can see there are a strange amount of repetitions (213). Is that normal or there's something wrong?

 

[edebe]

Edit:

I noticed that the great majority of the results don't have decimals, which seems quite strange to be honest giving the original values. Also you can see there are a strange amount of repetitions (213). Is that normal or there's something wrong?

 

[Anthony47]

You need to format Row 14 to show the desired decimals
The now updated file has been created using your data

Bye

 

[edebe]

You need to format Row 14 to show the desired decimals
The now updated file has been created using your data

Bye

I updated the rows and solved the decimal issue.

Though, I don't understand why it repeats always the sum of all the values (212.74) which instead should appear only once.