How to read data from another sheet and ignore blanks?

excelNewbie22

Well-known Member
Joined
Aug 4, 2021
Messages
534
Office Version
  1. 365
Platform
  1. Windows
hi!
i'm trying using this:
Excel Formula:
=FILTER('99'!L2:L150,'99'!L2:L150<>0)
but since in sheet 99 there's formula that join the cells g3:k3
Excel Formula:
=TEXTJOIN("-",TRUE,G3:K3)
the filter function read the array l2:l150 with the blanks
any solutions?

22
ABCDEFGHIJ
21234561-2-3-4-5-61-2-3-4-5-6
3 
47891011127-8-9-10-11-127-8-9-10-11-12
999
Cell Formulas
RangeFormula
J2:J4J2='999'!H2:H4
H2:H4H2=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
ABCDEFGHIJK
2123456
312345
412346
512356
612456
713456
823456
9789101112
107891011
117891012
127891112
1378101112
1479101112
1589101112
999


wanted result:
22
ABCDEFGHIJK
212345612345
312346
412356
512456
613456
723456
87891011127891011
97891012
107891112
1178101112
1279101112
1389101112
999
 
Last edited:

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
manage to solve it

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

thanks any way!!
 
Upvote 0
Solution

Forum statistics

Threads
1,224,823
Messages
6,181,173
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top