How to read data from another sheet and ignore blanks?

[excelNewbie22]

hi!
i'm trying using this:

=FILTER('99'!L2:L150,'99'!L2:L150<>0)

but since in sheet 99 there's formula that join the cells g3:k3

=TEXTJOIN("-",TRUE,G3:K3)

the filter function read the array l2:l150 with the blanks
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?

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

 

[excelNewbie22]

manage to solve it

=FILTER('99'!L2:L150,'99'!L2:L150<>"")

thanks any way!!