excelNewbie22
Well-known Member
- Joined
- Aug 4, 2021
- Messages
- 528
- Office Version
- 365
- Platform
- Windows
hi!
i got helped from johnnyL with the macro below, and it helped a lot,
and lately from Alex Blakenburg for ignoring blanks in the range,
(and sorry to say alex if you reading this, but when i tested it more,
it doesn't work properly, it gave me combinations of 5 and 2... it didn't ignore the blanks as it should)
i wonder if someone can help me further more with it,
cause i'm stuck....
is it allowed to address a user in the forum to my thread for getting his attention for try and help me out? in that case johnny
a-ignoring blank cells in the range, like a1:z1 when z1 is empty (or more like s1 to z1)
b-i want to add a function
(just like say countif(a1:a10,apple)
to access the macro, so i can "fill right",
it will be saving me tons of time,
something like
a1:z1 - range, 6=# of combinations, aa2=results presented
not a must, but will be nice too if:
c-use the range vertically or horiznally (a1:z1 or a1:a30)
d-in the header (aa1) present the number of results, aka counta(aa2:aa999999)
i got helped from johnnyL with the macro below, and it helped a lot,
and lately from Alex Blakenburg for ignoring blanks in the range,
(and sorry to say alex if you reading this, but when i tested it more,
it doesn't work properly, it gave me combinations of 5 and 2... it didn't ignore the blanks as it should)
i wonder if someone can help me further more with it,
cause i'm stuck....
is it allowed to address a user in the forum to my thread for getting his attention for try and help me out? in that case johnny
a-ignoring blank cells in the range, like a1:z1 when z1 is empty (or more like s1 to z1)
b-i want to add a function
(just like say countif(a1:a10,apple)
to access the macro, so i can "fill right",
it will be saving me tons of time,
something like
Excel Formula:
=GenComb(a1:z1,6,aa2)
not a must, but will be nice too if:
c-use the range vertically or horiznally (a1:z1 or a1:a30)
d-in the header (aa1) present the number of results, aka counta(aa2:aa999999)
VBA Code:
Option Explicit
'by johnnyL
' Inspired by some previous code from pgc01 https://www.mrexcel.com/board/threads/combination-help.277924/post-1379220
'
'https://www.mrexcel.com/board/threads/vba-to-generate-all-combinations-no-repeat-from-a-range.1215877/page-2#post-5946385
'+https://www.mrexcel.com/board/threads/ignoring-blanks-in-a-macro-range.1218927/
'--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sub AllCombinationsV2()
'
Dim StartTime As Double
StartTime = Timer ' Start the stop watch
'
Dim ArrayRow As Long
Dim DrawAmount As Long, TotalCombinations As Long
Dim OutputRow As Long
Dim PoolSize As Long
Dim NumbersPoolRange As Range
Dim OutputColumn As String
Dim OutputHeader As String
Dim NumbersDrawnArray As Variant, NumbersPoolArray As Variant, OutputArray As Variant
'
Set NumbersPoolRange = Range("cn1:dl1") ' <--- Set this to the range of the pool #s to use
DrawAmount = "6" ' <--- set the amount of #s to pick in this cell
OutputColumn = "bd" ' <--- Set this to the column letter that you want to display results to
OutputRow = 2 ' <--- Set this to the row # to start displaying the combinations
' ----- Modification
Dim NumbersPoolArrayTemp As Variant
Dim iPoolArray As Long
NumbersPoolArrayTemp = NumbersPoolRange
ReDim NumbersPoolArray(1 To 1, 1 To UBound(NumbersPoolArrayTemp, 2))
For iPoolArray = 1 To UBound(NumbersPoolArrayTemp, 2)
If NumbersPoolArrayTemp(1, iPoolArray) <> "" Then
PoolSize = PoolSize + 1
NumbersPoolArray(1, PoolSize) = NumbersPoolArrayTemp(1, iPoolArray)
End If
Next iPoolArray
' PoolSize = NumbersPoolRange.Columns.Count ' Get the amount of numbers in the pool - replaced by counter
OutputHeader = DrawAmount & " of " & PoolSize ' <--- Set this to the header for the output column
'
Application.ScreenUpdating = False ' Turn screen updationg off
'
TotalCombinations = Application.WorksheetFunction.Combin(PoolSize, DrawAmount) ' Get total # of combinations that will be produced
'
ReDim Preserve NumbersPoolArray(1 To 1, 1 To PoolSize)
' ----- End of modification
'Range("A3").Value = "Total # of combinations" ' Display header of 'Total # of combinations'
'Range("aa1").Value = TotalCombinations ' Display # of combinations that will be generated
'
ReDim NumbersDrawnArray(1 To DrawAmount) ' Establish size of 1D one based NumbersDrawnArray
ReDim OutputArray(1 To TotalCombinations, 1 To DrawAmount) ' Establish size of 2D one based OutputArray
'
Call GetAllCombinations(NumbersPoolArray, DrawAmount, NumbersDrawnArray, ArrayRow, OutputArray, 1, 1) ' Calculate the Combinations
'
Range(OutputColumn & OutputRow - 1).Value = OutputHeader ' Display output header
Range(OutputColumn & OutputRow).Resize(TotalCombinations, 1).Value = OutputArray ' Display results to sheet
'
ActiveSheet.UsedRange.EntireColumn.AutoFit ' Autofit all column widths on the sheett
'
Application.ScreenUpdating = True ' Turn screen updationg back on
'
'Debug.Print TotalCombinations & " combinations completed in " & Timer - StartTime & " seconds." ' Display the completion time in the 'Immediate' window ... CTRL+G in the VBE window
'MsgBox TotalCombinations & " combinations completed in " & Timer - StartTime & " seconds." ' Display the combination count & time to complete to the user
End Sub
Sub GetAllCombinations(ByVal NumbersPoolArray As Variant, ByVal DrawAmount As Long, ByVal NumbersDrawnArray As Variant, _
ByRef ArrayRow As Long, ByRef OutputArray As Variant, ByVal NumbersPoolColumn As Long, ByVal DrawNumber As Long)
'
Dim ArrayColumn As Long
'
For NumbersPoolColumn = NumbersPoolColumn To UBound(NumbersPoolArray, 2) ' Loop through the NumbersPoolArray columns
NumbersDrawnArray(DrawNumber) = NumbersPoolArray(1, NumbersPoolColumn) ' Save NumbersPoolArray(1, NumbersPoolColumn) value to NumbersDrawnArray(DrawNumber)
'
If DrawNumber = DrawAmount Then ' If last DrawNumber of DrawAmount has been reached then ...
ArrayRow = ArrayRow + 1 ' Increment ArrayRow
'
For ArrayColumn = 1 To DrawAmount ' Loop through NumbersDrawnArray columns
OutputArray(ArrayRow, 1) = OutputArray(ArrayRow, 1) & NumbersDrawnArray(ArrayColumn) & "-" ' Save NumbersDrawnArray column value & delimiter to OutputArray
Next ' Loop back
'
OutputArray(ArrayRow, 1) = Left$(OutputArray(ArrayRow, 1), Len(OutputArray(ArrayRow, 1)) - 1) ' Remove the last delimiter '-' from the OutputArray string
Else ' Else ...
Call GetAllCombinations(NumbersPoolArray, DrawAmount, NumbersDrawnArray, ArrayRow, _
OutputArray, NumbersPoolColumn + 1, DrawNumber + 1) ' Recursive loop to get next value of NumbersDrawnArray
End If
Next ' Loop back
End Sub