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?



4 Replies


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


=FILTER(C2:C11, C2:C11<>"")

@L z. 

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?



best response confirmed by Hans Vogelaar (MVP)


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:

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


* There's a quite complex way to do this with a 365 LAMBDA function but I'm not the best person to explain this

@L z. 


Thank you!