excelNewbie22
Well-known Member
- Joined
- Aug 4, 2021
- Messages
- 534
- Office Version
- 365
- Platform
- Windows
hi!
i'm trying using this:
but since in sheet 99 there's formula that join the cells g3:k3
the filter function read the array l2:l150 with the blanks
any solutions?
as alternative solution
can this vba by johnnyL be edited to return results in same lines without creating blanks?
right now is like this:
wanted result:
i'm trying using this:
Excel Formula:
=FILTER('99'!L2:L150,'99'!L2:L150<>0)
Excel Formula:
=TEXTJOIN("-",TRUE,G3:K3)
any solutions?
22 | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
2 | 1 | 2 | 3 | 4 | 5 | 6 | 1-2-3-4-5-6 | 1-2-3-4-5-6 | ||||
3 | ||||||||||||
4 | 7 | 8 | 9 | 10 | 11 | 12 | 7-8-9-10-11-12 | 7-8-9-10-11-12 | ||||
999 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
J2:J4 | J2 | ='999'!H2:H4 |
H2:H4 | H2 | =TEXTJOIN("-",TRUE,A2:F2) |
Dynamic array formulas. |
as alternative solution
can this vba by johnnyL be edited to return results in same lines without creating blanks?
VBA Code:
Sub excelNewbie22V2()
'
Dim InputNumbersRow As Long
Dim LastRow As Long, StartRow As Long
Dim SourceArray() As Long, OutputArray(1 To 6, 1 To 5) As Long
Dim OutputRow As Long
Dim SourceArrayColumn As Long, SourceArrayRow As Long
Dim OutputColumn As String
Dim InputNumbersArray As Variant
'
StartRow = 2 ' <--- Set this to the starting row of numbers to use
OutputColumn = "G" ' <--- Set this to the Column letter to display results to
LastRow = Range("A" & Rows.Count).End(xlUp).Row ' Get last row # of numbers to use
SourceArray = GetCombinations(6, 5) ' Load SourceArray with all non repeating 5 out of 6 combinations
'
InputNumbersArray = Range("A" & StartRow & ":F" & LastRow) ' Save numbers to use to InputNumbersArray
'
Range("A" & StartRow & ":F" & LastRow).ClearContents ' Erase the numbers to use range
'
OutputRow = -4 ' Initialize OutputRow
'
For InputNumbersRow = LBound(InputNumbersArray, 1) To UBound(InputNumbersArray, 1) ' Loop through the rows of numbers to use
OutputRow = OutputRow + 6 ' Increment the OutputRow
'
Range("A" & OutputRow).Resize(1, 6) = Array(InputNumbersArray(InputNumbersRow, 1), _
InputNumbersArray(InputNumbersRow, 2), InputNumbersArray(InputNumbersRow, 3), _
InputNumbersArray(InputNumbersRow, 4), InputNumbersArray(InputNumbersRow, 5), _
InputNumbersArray(InputNumbersRow, 6)) ' Display row of numbers to use to the sheet
'
OutputRow = OutputRow + 1 ' Increment the OutputRow
'
For SourceArrayRow = 1 To 6 ' Loop through rows of the SourceArray
For SourceArrayColumn = 1 To 5 ' Loop through columns of the SourceArray
Select Case SourceArray(SourceArrayRow, SourceArrayColumn)
Case 1: OutputArray(SourceArrayRow, SourceArrayColumn) = _
InputNumbersArray(InputNumbersRow, 1) ' Perform replacement array values ...
Case 2: OutputArray(SourceArrayRow, SourceArrayColumn) = _
InputNumbersArray(InputNumbersRow, 2)
Case 3: OutputArray(SourceArrayRow, SourceArrayColumn) = _
InputNumbersArray(InputNumbersRow, 3)
Case 4: OutputArray(SourceArrayRow, SourceArrayColumn) = _
InputNumbersArray(InputNumbersRow, 4)
Case 5: OutputArray(SourceArrayRow, SourceArrayColumn) = _
InputNumbersArray(InputNumbersRow, 5)
Case 6: OutputArray(SourceArrayRow, SourceArrayColumn) = _
InputNumbersArray(InputNumbersRow, 6)
End Select
Next ' Loop back
Next ' Loop back
'
Range(OutputColumn & OutputRow).Resize(UBound(OutputArray), 5).Value = OutputArray ' Display results to sheet
Next ' Loop back
End Sub
Function GetCombinations(lNumber As Long, lNoChosen As Long) As Long()
Dim lOutput() As Long, lCombinations As Long
Dim i As Long, j As Long, k As Long
lCombinations = WorksheetFunction.Combin(lNumber, lNoChosen)
ReDim lOutput(1 To lCombinations, 1 To lNoChosen)
For i = 1 To lNoChosen
lOutput(1, i) = i
Next i
For i = 2 To lCombinations
For j = 1 To lNoChosen
lOutput(i, j) = lOutput(i - 1, j)
Next j
For j = lNoChosen To 1 Step -1
lOutput(i, j) = lOutput(i, j) + 1
If lOutput(i, j) <= lNumber - (lNoChosen - j) Then Exit For
Next j
For k = j + 1 To lNoChosen
lOutput(i, k) = lOutput(i, k - 1) + 1
Next k
Next i
GetCombinations = lOutput
End Function
right now is like this:
22 | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
2 | 1 | 2 | 3 | 4 | 5 | 6 | |||||||
3 | 1 | 2 | 3 | 4 | 5 | ||||||||
4 | 1 | 2 | 3 | 4 | 6 | ||||||||
5 | 1 | 2 | 3 | 5 | 6 | ||||||||
6 | 1 | 2 | 4 | 5 | 6 | ||||||||
7 | 1 | 3 | 4 | 5 | 6 | ||||||||
8 | 2 | 3 | 4 | 5 | 6 | ||||||||
9 | 7 | 8 | 9 | 10 | 11 | 12 | |||||||
10 | 7 | 8 | 9 | 10 | 11 | ||||||||
11 | 7 | 8 | 9 | 10 | 12 | ||||||||
12 | 7 | 8 | 9 | 11 | 12 | ||||||||
13 | 7 | 8 | 10 | 11 | 12 | ||||||||
14 | 7 | 9 | 10 | 11 | 12 | ||||||||
15 | 8 | 9 | 10 | 11 | 12 | ||||||||
999 |
wanted result:
22 | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
2 | 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | ||
3 | 1 | 2 | 3 | 4 | 6 | ||||||||
4 | 1 | 2 | 3 | 5 | 6 | ||||||||
5 | 1 | 2 | 4 | 5 | 6 | ||||||||
6 | 1 | 3 | 4 | 5 | 6 | ||||||||
7 | 2 | 3 | 4 | 5 | 6 | ||||||||
8 | 7 | 8 | 9 | 10 | 11 | 12 | 7 | 8 | 9 | 10 | 11 | ||
9 | 7 | 8 | 9 | 10 | 12 | ||||||||
10 | 7 | 8 | 9 | 11 | 12 | ||||||||
11 | 7 | 8 | 10 | 11 | 12 | ||||||||
12 | 7 | 9 | 10 | 11 | 12 | ||||||||
13 | 8 | 9 | 10 | 11 | 12 | ||||||||
999 |
Last edited: