Forum Discussion

rhockman's avatar
rhockman
Brass Contributor
Aug 18, 2022
Solved

Data List

I have data in a long column with many blank fields.  I want a list created eliminating the blank fields and keeping all the data.  How do I do this?

 

Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Aug 19, 2022

    rhockman 

    With the below example, if your question is, can I do something like =FILTER(B2:E11, B2:E11 <> "") then the answer is no*

     

    What you can do (in G-J) is to match the month (SEP, OCT...) in B-K, get the values then filter them

    In G2 then copy right:

     

    =LET(
    MonthData, XLOOKUP(G1, $B1:$E1, $B2:$E11),
    FILTER(MonthData, MonthData <> "")
)

     

     

    * Doable with 365, ex:

    =TRANSPOSE(
  TEXTSPLIT(
    TEXTJOIN(",",,BYCOL(MonthData, LAMBDA(r, TEXTJOIN(";",,r)))),
    ";",",",,,""
  )
)
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Aug 18, 2022

    rhockman 

    Assuming your run Excel 2021 or 365, with your data in C2:C11:

     

    =FILTER(C2:C11, C2:C11<>"")
    • rhockman's avatar
      rhockman
      Brass Contributor
      Aug 18, 2022

      Lorenzo 

      That's great.  Can I use the same filter (B-K) for multiple months and add the changes in the same order into one list?

       

      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Aug 19, 2022

        rhockman 

        With the below example, if your question is, can I do something like =FILTER(B2:E11, B2:E11 <> "") then the answer is no*

         

        What you can do (in G-J) is to match the month (SEP, OCT...) in B-K, get the values then filter them

        In G2 then copy right:

         

        =LET(
    MonthData, XLOOKUP(G1, $B1:$E1, $B2:$E11),
    FILTER(MonthData, MonthData <> "")
)

         

         

        * Doable with 365, ex:

        =TRANSPOSE(
  TEXTSPLIT(
    TEXTJOIN(",",,BYCOL(MonthData, LAMBDA(r, TEXTJOIN(";",,r)))),
    ";",",",,,""
  )
)

Resources