without using "advanced filtiering"

Hi, I need a help to solve this problem.

Let's say there are three columns A, B, C. I always put the data in Column A. And the each cell in Column B has formula in it, which is using the data from cells in Column A. And Column C contains the cells which is extracted from Column B. I, specifically, tried to extracted non-blank cells (80% of the cells in Column B left as blank.) from Column B into Column C. I have done that with "advanced filtering", but once I change the input data in Column A, I though Column C would changed accordingly. But I was wrong, it remains same and is holding a previous value.

Is there any easier way to do this? Or is there any function that will do this kind of job?

Thanks a lot for the help, I mean, a lot.

Try the following array formula...

C1, copied down:

=IF(ROW()-ROW($C$1)+1<=COUNTIF($B$1:$B$100,">0"),INDEX($B$1:$B$100,SMALL(IF($B$1:$B$100<>"",ROW($B$1:$B$100)-CELL("row",$B$1)+1),ROW()-ROW($C$1)+1)),"")

...confirmed with CONTROL+SHIFT+ENTER. Adjust the range accordingly.

Hope this helps!

Originally Posted by hanshaus

Hi, I need a help to solve this problem.

Let's say there are three columns A, B, C. I always put the data in Column A. And the each cell in Column B has formula in it, which is using the data from cells in Column A. And Column C contains the cells which is extracted from Column B. I, specifically, tried to extracted non-blank cells (80% of the cells in Column B left as blank.) from Column B into Column C. I have done that with "advanced filtering", but once I change the input data in Column A, I though Column C would changed accordingly. But I was wrong, it remains same and is holding a previous value.

Is there any easier way to do this? Or is there any function that will do this kind of job?

Thanks a lot for the help, I mean, a lot.