Formula not working as expected

[Russk68]

Hi All
The link below shows 2 screenshots of the same formula with 1 working as expected and not the other. The top screen shot with the column circled in green shows the results of the column before it, condensed to the first 3 rows. The bottom screen shot with the column circled in red using the same formula does not condense the results of the column before it.
The screen shots provide the formula bar of the top cell in each column. The formulas in the column that is not working as expected were dragged down and each ROW in the formula do increase by 1 and are all array formulas. The calculations are set to auto as well.

I apologize if providing a link is not the proper way to do this. I also spent an hour trying to figure out how to attach screen shots. I tried Excel Jeanie but I couldn't figure it out and I don't think it can capture the formula bar.

https://www.dropbox.com/s/cmc1fcrgvudrabm/2017-12-05_13-41-21.png?dl=0

I've been stumped on this for a few hours and would appreciate any help.

Thank you!

 

[Marcelo Branco]

I would change
IF(ISTEXT($BF$1:$BF$2199),...

by
IF($BF$1:$BF$2199<>"",...

A more robust formula
=IFERROR(INDEX($BF$1:$BF$2199,SMALL(IF($BF$1:$BF$2199<>"",ROW($BF$1:$BF$2199)-ROW($BF$1)+1),ROWS(BF$1:BF1))),"")
Ctrl+Shift+Enter

M.

 

[Russk68]

Hi and thank you for your help.
When I pasted the formula and dragged it down it worked. When I recalculated the sheet, it stop working. I remember it working with the other formula as well and then it stopped working.
I provided a link with your formula.

https://www.dropbox.com/s/k5xa0uu3oc8t6x1/Screen Shot 2017-12-05 at 3.12.12 PM.png?dl=0

 

[Russk68]

I pasted your formula in another column and updated the rows and it works. I then pasted it back from there and updated the rows and it works. Something weird on my end.

Thank you very much for your help!

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.

 

[Russk68]

Just an update
After many hours of frustration I discovered that hidden 0's results were causing the spaces.
Works great!
Thank you!