Hi all,
I can do some basic VBA and would love to fix this myself, but I just can't (yet)
I believe it requires some real Excel wizard master.
So here is the thing:
I have tracked down a wonderful macro (I think it was originally created by member shg)
This macro generates all possible combinations from input by a single click
For example:
It works truly fantastic.
However for me personally the limitation to 1 million rows is an obstacle.
Therefore I tried to add a condition that will not allow every combination to be listed.
This code however is so complex to me... just can't find anything even near a solution.
So what I want to add is a check that involves a list of values on sheet "Value"
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Option Name[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]Option A - 1[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Option B - 1[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Option C - 1[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Option C - 2[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Option D - 1[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]Option E - 1[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Option E - 2[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]Option E - 3[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]Option E - 4[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Option F - 1[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]Option G - 1[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]Option G - 2[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]Option G - 3[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD]Option G - 4[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Option G - 5[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]Option G - 6[/TD]
[TD]22[/TD]
[/TR]
</tbody>[/TABLE]
The VBA should list only combinations of which the combined (SUM) value is not exceeding 85.
In fact I have more limitations in mind to narrow things down, but just this one should be able to set me off to create so much more.
This is the original code created by shg:
So hopefully any of you is willing to help me out on this.
thank you very much in advance!! All help greatly appreciated
I can do some basic VBA and would love to fix this myself, but I just can't (yet)
I believe it requires some real Excel wizard master.
So here is the thing:
I have tracked down a wonderful macro (I think it was originally created by member shg)
This macro generates all possible combinations from input by a single click
For example:
It works truly fantastic.
However for me personally the limitation to 1 million rows is an obstacle.
Therefore I tried to add a condition that will not allow every combination to be listed.
This code however is so complex to me... just can't find anything even near a solution.
So what I want to add is a check that involves a list of values on sheet "Value"
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Option Name[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]Option A - 1[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Option B - 1[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Option C - 1[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Option C - 2[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Option D - 1[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]Option E - 1[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Option E - 2[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]Option E - 3[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]Option E - 4[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Option F - 1[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]Option G - 1[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]Option G - 2[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]Option G - 3[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD]Option G - 4[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Option G - 5[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]Option G - 6[/TD]
[TD]22[/TD]
[/TR]
</tbody>[/TABLE]
The VBA should list only combinations of which the combined (SUM) value is not exceeding 85.
In fact I have more limitations in mind to narrow things down, but just this one should be able to set me off to create so much more.
This is the original code created by shg:
Code:
Option Explicit
Const sTitle As String = "shg Cartesian Product"
Sub CartesianProduct() ' shg 2012, 2013
' Choose one from col A, one from col B, ...
Dim rInp As Range
Dim avInp As Variant ' ragged input list
Dim nCol As Long ' # columns in list
Dim rOut As Range ' output range
Dim iCol As Long ' column index
Dim iRow As Long ' row index
Dim aiCum() As Long ' cum count of arrangements from right to left
Dim aiCnt() As Long ' count of items in each column
Dim iArr As Long ' arrangement number
Dim avOut As Variant ' output buffer
Application.ScreenUpdating = False
Set rInp = Range("rgnInp")
If VarType(rInp.Value) = vbEmpty Then
MsgBox Prompt:="No input!", _
Buttons:=vbOKOnly, _
Title:=sTitle
Exit Sub
End If
Set rInp = rInp.CurrentRegion
If rInp.Columns.Count < 2 Or rInp.Rows.Count < 2 Then
MsgBox Prompt:="Must have more than one row and more than one columns!", _
Buttons:=vbOKOnly, _
Title:=sTitle
Exit Sub
End If
With rInp
.Style = "Input"
avInp = .Value
nCol = .Columns.Count
Set rOut = .Resize(1).Offset(.Rows.Count + 1)
Range(rOut.Offset(-1, -1), Cells(Rows.Count, Columns.Count)).Clear
End With
ReDim aiCum(1 To nCol + 1)
ReDim aiCnt(1 To nCol)
aiCum(nCol + 1) = 1
For iCol = nCol To 1 Step -1
For iRow = 1 To UBound(avInp, 1)
If IsEmpty(avInp(iRow, iCol)) Then Exit For
aiCnt(iCol) = aiCnt(iCol) + 1
Next iRow
aiCum(iCol) = aiCnt(iCol) * aiCum(iCol + 1)
Next iCol
If aiCum(1) > Rows.Count - rOut.Row + 1 Then
MsgBox Prompt:=Format(aiCum(1), "#,##0") & _
" is too many rows!", _
Buttons:=vbOKOnly, Title:=sTitle
Exit Sub
End If
ReDim avOut(1 To aiCum(1), 1 To nCol)
For iArr = 1 To aiCum(1)
For iCol = 1 To nCol
avOut(iArr, iCol) = avInp((Int((iArr - 1) * aiCnt(iCol) / aiCum(iCol))) Mod aiCnt(iCol) + 1, iCol)
Next iCol
Next iArr
With rOut.Resize(aiCum(1), nCol)
.NumberFormat = "@"
.Value = avOut
.Style = "Code"
.Cells(1, 0).Value = 1
.Cells(2, 0).Value = 2
.Cells(1, 0).Resize(2).AutoFill .Columns(0)
End With
ActiveWindow.FreezePanes = False
rOut.EntireColumn.AutoFit
ActiveSheet.UsedRange
Beep
End Sub
So hopefully any of you is willing to help me out on this.
thank you very much in advance!! All help greatly appreciated
Last edited: