Creating a list that filters out Blanks

[Steeden87]

Hello all,

I have been racking my brain for a few days trying to solve an issue with a spreadsheet that I am having.

There is a list generate by a formulas (From True/False) that returns the result, but I need it to filter out blanks and make a consecutive list. See an example below with the formula I am using.

I have tried using this Formula: =IFERROR(INDEX($A$2:$A$10,SMALL(IF(ISTEXT($A$2:$A$10),ROW($A$1:$A$9),""),ROW(A1))),"") - Found here: https://www.myonlinetraininghub.com/excel-remove-blank-cells-from-a-range

But, this formula only works for Data entered manually and not formulas as a column with a formula (Returning "") isn't counted as a blank cell.

Any suggestions to generate this without the blanks spaces would be greatly appreciated and would save my department within the Ministry of Education hours of time whilst generating reports having to manually sort this.

Thank you!

 

[Marcelo Branco]

Say your data are in A2:A100 - to create a list without blanks in column B, beginning in B2, try

Array formula in B2 copied down
=IFERROR(INDEX(A$2:A$100,SMALL(IF(A$2:A$100<>"",ROW(A$2:A$100)-ROW(A$2)+1),ROWS(B$2:B2))),"")
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[Peter_SSs]

For the same setup Marcelo described, an alternative is this version that doesn't require the Ctrl+Shift+Enter confirmation.

=IFERROR(INDEX(A$2:A$100,AGGREGATE(15,6,(ROW(A$2:A$100)-ROW(A$2)+1)/(A$2:A$100<>""),ROWS(B$2:B2))),"")

 

[Steeden87]

Say your data are in A2:A100 - to create a list without blanks in column B, beginning in B2, try

Array formula in B2 copied down
=IFERROR(INDEX(A$2:A$100,SMALL(IF(A$2:A$100<>"",ROW(A$2:A$100)-ROW(A$2)+1),ROWS(B$2:B2))),"")
confirmed with Ctrl+Shift+Enter, not just Enter

M.

AMAZING!

Thank you both, it worked perfectly!

I really appreciate your help. I have been tasked with automating all the reports we do and I thought I had come to a dead end with this issue.

Thank you!

 

[Marcelo Branco]

You are welcome. Glad to help

M.

 

[Peter_SSs]

Thank you both,..

"You are welcome" from me too.