SOLVED

Data List

Contributor

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?

 

Capture.PNG

4 Replies

@rhockman 

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?

 

Capture.PNG

best response confirmed by Hans Vogelaar (MVP)
Solution

@rhockman 

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

 

_Screenshot.png

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 <> "")
)

 

* 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!