Special combinations

[RIC63]

Good morning,

I have the attached file which initially generated the combinations between three lists of 6 elements by taking 1 element at a time from each list. I modified it with the intention of generating all combinations -without repetitions- for 12 lists.


Certainly by taking 1 element from each list the number of combinations is surely high but I would have to take a min and a Max of elements - see lines 13 and 14 - from each list and respecting a total min Max - see E31 and E32 -.


Finally, instead of having the output in the Excel file where the number of lines is certainly not sufficient to contain it, I would like the list of combinations to be written in a text file.




I tried to look for something similar but I couldn't, can anyone tell me if it's possible and possibly help?


thanks in advance for the support

[Paul_Hossler]

Maybe something like this

I think that there's 6^12 = 2,176,782,336 combanations, so thats a lot of computer time and a lot of disc space

It might be better to rethink where you want to go with this





'always a good idea and usually recommended
Option Explicit


Sub ListAllCombinations()
'without the 'As Range' on every one, the system assumed that they're Variant
Dim xDRg1 As Range, xDRg2 As Range, xDRg3 As Range, xDRg4 As Range, xDRg5 As Range, xDRg6 As Range
Dim xDRg7 As Range, xDRg8 As Range, xDRg9 As Range, xDRg10 As Range, xDRg11 As Range, xDRg12 As Range

Dim xStr As String

Dim xFN1 As Long, xFN2 As Long, xFN3 As Long, xFN4 As Long, xFN5 As Long, xFN6 As Long
Dim xFN7 As Long, xFN8 As Long, xFN9 As Long, xFN10 As Long, xFN11 As Long, xFN12 As Long

Dim xSV1 As String, xSV2 As String, xSV3 As String, xSV4 As String, xSV5 As String, xSV6 As String
Dim xSV7 As String, xSV8 As String, xSV9 As String, xSV10 As String, xSV11 As String, xSV12 As String

'file stuff
Dim iFilenum As Long
Dim sFileName As String, sLine As String
Dim iCounter As Long
Dim dTotal As Double




Set xDRg1 = Range("A2:A7") '1 column data
Set xDRg2 = Range("B2:B7") '2 column data
Set xDRg3 = Range("C2:C7") '3 column data
Set xDRg4 = Range("D2:D7") '4 column data
Set xDRg5 = Range("E2:E7") '5 column data
Set xDRg6 = Range("F2:F7") '6 column data
Set xDRg7 = Range("G2:G7") '7 column data
Set xDRg8 = Range("H2:H7") '8 column data
Set xDRg9 = Range("I2:I7") '9 column data
Set xDRg10 = Range("J2:J7") '10 column data
Set xDRg11 = Range("K2:K7") '11 column data
Set xDRg12 = Range("L2:L7") '12 column data


xStr = "," 'Separator


'file
iCounter = 1
dTotal = 6# ^ 12#
iFilenum = FreeFile
sFileName = ThisWorkbook.Path & Application.PathSeparator & ThisWorkbook.Name & ".csv"

On Error Resume Next
Application.DisplayAlerts = False
Kill sFileName
Application.DisplayAlerts = True
On Error GoTo 0

Open sFileName For Output As #iFilenum




For xFN1 = 1 To xDRg1.Count
xSV1 = xDRg1.Item(xFN1).Text

For xFN2 = 1 To xDRg2.Count
xSV2 = xDRg2.Item(xFN2).Text

For xFN3 = 1 To xDRg3.Count
xSV3 = xDRg3.Item(xFN3).Text

For xFN4 = 1 To xDRg4.Count
xSV4 = xDRg4.Item(xFN4).Text

For xFN5 = 1 To xDRg5.Count
xSV5 = xDRg5.Item(xFN5).Text

For xFN6 = 1 To xDRg6.Count
xSV6 = xDRg6.Item(xFN6).Text

For xFN7 = 1 To xDRg7.Count
xSV7 = xDRg7.Item(xFN7).Text

For xFN8 = 1 To xDRg8.Count
xSV8 = xDRg8.Item(xFN8).Text

For xFN9 = 1 To xDRg9.Count
xSV9 = xDRg9.Item(xFN9).Text

For xFN10 = 1 To xDRg10.Count
xSV10 = xDRg10.Item(xFN10).Text

For xFN11 = 1 To xDRg11.Count
xSV11 = xDRg11.Item(xFN11).Text

For xFN12 = 1 To xDRg12.Count
xSV12 = xDRg12.Item(xFN12).Text


sLine = xSV1 & xStr & xSV2 & xStr & xSV3 & xStr & xSV4 & xStr & xSV5 & xStr & xSV6 & xStr & xSV7 & xStr & xSV8 & xStr & xSV9 & xStr & xSV10 & xStr & xSV11 & xStr & xSV12

If iCounter Mod 1000 = 1 Then
Application.StatusBar = "Case #" & Format(iCounter, "#,##0") & " (" & Format(CDbl(iCounter) / dTotal, "#0.00%") & ")"
DoEvents
End If

Print #iFilenum, sLine
iCounter = iCounter + 1

Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next

Close iFilenum

Application.StatusBar = False

MsgBox "Done"
End Sub

[p45cal]

In your code, the number of items in each combination is limited to 12 because only one item is used from each of 12 lists.
Now that you can take more than one item from each list, is each combination still limited to 12?

[RIC63]

Thanks for your help Paul


by writing the data to a file you solved the problem of the limitation of 108576 rows in Excel, thanks


now it would be a nice thing to be able to condition the generation of records by implementing the conditions indicated on lines 13, 14 and in E31/E32 and I honestly don't know if it's feasible.

[RIC63]

Hi p45cal
nice to hear from you again and thanks again for your interest,


yes, if it is feasible to impose the min and max quantities to be taken from each list and check the total of their summation, the records generated will have different lengths, i.e. they will be composed of min 20 to max 29 elements as specified in E31 E32


I hope to read good news


greetings and thanks again

[Paul_Hossler]

1. To improve performance I moved data in and out of arrays

2.
yes, if it is feasible to impose the min and max quantities to be taken from each list and check the total of their summation, the records generated will have different lengths, i.e. they will be composed of min 20 to max 29 elements as specified in E31 E32

Confused. Your code picks 1 from col A, 1 from Col B, ..., 1 from Col L. There are always 12 enteries since there are 12 columns

Are you saying that you want between 20 and 29 enteries total, with 1, 2, or 3 from col A, 1, 2, or 3 from Col B, ..., 1, 2, 3, 4, 5 or 6 from Col L???

WHY???




'always a good idea and usually recommended
Option Explicit


Const xStr As String = "," 'Separator


Dim rData As Range
Dim aryData As Variant
Dim aryNumColEnteries() As Long
Dim aryOut() As String




Sub ListAllCombinations()

Dim iCol As Long
Dim i1 As Long, i2 As Long, i3 As Long, i4 As Long, i5 As Long, i6 As Long
Dim i7 As Long, i8 As Long, i9 As Long, i10 As Long, i11 As Long, i12 As Long

Dim aryValue() As String

'file stuff
Dim iFilenum As Long
Dim sFileName As String, sLine As String
Dim iCounter As Long
Dim dTotal As Double



Set rData = Cells(1, 1).CurrentRegion
ReDim aryNumColEnteries(1 To rData.Columns.Count)
ReDim aryValue(1 To rData.Columns.Count)
ReDim aryI(1 To rData.Columns.Count)

For iCol = 1 To rData.Columns.Count
aryNumColEnteries(iCol) = Application.WorksheetFunction.CountA(rData.Columns(iCol)) ' includes headers
Next iCol

aryData = rData.Value


'file
iCounter = 0
dTotal = 6# ^ 12#
iFilenum = FreeFile
iCounter = InStrRev(ThisWorkbook.FullName, ".")
sFileName = Left(ThisWorkbook.FullName, iCounter) & "csv"



On Error Resume Next
Application.DisplayAlerts = False
Kill sFileName
Application.DisplayAlerts = True
On Error GoTo 0

Open sFileName For Output As #iFilenum


' build combo
For i1 = 2 To aryNumColEnteries(1)
aryValue(1) = aryData(i1, 1)

For i2 = 2 To aryNumColEnteries(2)
aryValue(2) = aryData(i2, 2)

For i3 = 2 To aryNumColEnteries(3)
aryValue(3) = aryData(i3, 3)

For i4 = 2 To aryNumColEnteries(4)
aryValue(4) = aryData(i4, 4)

For i5 = 2 To aryNumColEnteries(5)
aryValue(5) = aryData(i5, 5)

For i6 = 2 To aryNumColEnteries(6)
aryValue(6) = aryData(i6, 6)

For i7 = 2 To aryNumColEnteries(7)
aryValue(7) = aryData(i7, 7)

For i8 = 2 To aryNumColEnteries(8)
aryValue(8) = aryData(i8, 8)

For i9 = 2 To aryNumColEnteries(9)
aryValue(9) = aryData(i9, 9)

For i10 = 2 To aryNumColEnteries(10)
aryValue(10) = aryData(i10, 10)

For i11 = 2 To aryNumColEnteries(11)
aryValue(11) = aryData(i11, 11)

For i12 = 2 To aryNumColEnteries(12)
aryValue(12) = aryData(i12, 12)

sLine = Join(aryValue, xStr)

If iCounter Mod 1000 = 0 Then
Application.StatusBar = "Case #" & Format(iCounter, "#,##0") & " (" & Format(CDbl(iCounter) / dTotal, "#0.00%") & ")"
DoEvents
End If

Print #iFilenum, sLine

iCounter = iCounter + 1

Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next

Close iFilenum

Application.StatusBar = False

MsgBox "Done"
End Sub

[RIC63]

Morning Paul


Confused. Your code picks 1 from col A, 1 from Col B, ..., 1 from Col L. There are always 12 enteries since there are 12 columns

Are you saying that you want between 20 and 29 enteries total, with 1, 2, or 3 from col A, 1, 2, or 3 from Col B, ..., 1, 2, 3, 4, 5 or 6 from Col L???

yes you are right , is what I want..if feasible , I need this kind of composition for my statistical computation ..not records made of 12 elements

thanks Paul

[RIC63]

While I continue to search for a solution I found the code I attach, I don't have the knowledge to modify it and add it to the first code but I hope it can be of help/suggestion to achieve the goal

[p45cal]

they will be composed of min 20 to max 29 elements as specified in E31 E32Thank you; yes I discovered the answer to my question in your workbook.

I hope to read good news
I'm sorry to say I don't have it. While exploring possibilities I wanted to get an impression of the size of this task:
Taking 1 item each from 12 lists of 6 items requires some 2,176,782,336 iterations (2 billion). I never let the code run to its completion - it would have taken tens of hours.
Moving this from 1 item per list to 1 or 2 or 3 etc. items per list as per your workbook would need 119,279,358,384,576,000,000 iterations (edit: although a number of the loops could be exited early if 29 items has already been picked), thats 55 billion times more than just 1 item per list.
I grant that you wouldn't output all of these combinations because a good number wouldn't satisfy the 20 to 29 elements, however this 'brute force' method would take longer than my lifetime, even if the code did absolutely nothing inside the loops. I hope I've got my arithmetic right.
So I feel there might be a non brute-force method but I don't know it.
I'll still have a think about how to code for such loops but I'd never suggest it as a solution as it would never complete in time.
Another question is how big would your final text file be? Would it fit on your hard drive?

OK. Burning questions: What are you going to do with the results? Could you give us some context as to what this is about?

[georgiboy]

119,279,358,384,576,000,000 = one hundred and nineteen quintillion, two hundred and seventy nine quadrillion, three hundred and fifty eight trillion, three hundred and eighty four billion, five hundred and seventy six million... Wow

[RIC63]

I didn't start doing any calculations, I just thought that taking a minimum of 20 total elements among the 12 lists means that at least 2 elements must be taken from at least 8 lists then since I also have tests where the quantities to be taken from each list range from a min of 2 to a Max of 5/6 elements with a total between 37 and 44....I thought it was possible to tackle


Thanks anyway to everyone for what you suggested and for the support