need help in fine-tuning a formula that removes blank cells in a column range

[naira]

hi!

I need help in fine tuning a formula that removes blank cells from
column range.

_CODE:

=IF(ROWS(B$2:B2)>COUNTIF($A$2:$A$52,"?*"),"",INDEX($A$2:$A$52,MATCH(1,INDEX(($A$2:$A$52<>"")*ISNA(MATCH($A$2:$A$52,$B$1:$B1,1)),0),0)))

Problem is that each value is appearing uniquely in the result eve
though they might be appearing multiple times in the original range. An
suggestions to make the values appear sequentially in the order the
appear and multiple times after emoving blanks?

Alternately, if someone has a better formula would appreciate if it ca
be shared.

_FOLLOWING_LIMITATIONS_MAY_PLEASE_BE_CONSIDERED:
1. No VBA/ Macros please since they are volatile and tend to disabl
undo option.

2. No array formulas please (those committed with CTRL+SHIFT+ENTER)
since my current solution already uses array formula and that ha
considerably slowed down calculations. My actual worksheet has abou
100,000 cells with the array formula updated on a real time basis.

3. The blank cells will actually not be blank but will be containing
formula and the cells are blank since the result of their calculation i
blank.

Sample file attached.

Regards,

Nair

+-------------------------------------------------------------------
|Filename: Capture.JPG
|Download: http://www.excelbanter.com/attachment.php?attachmentid=868
+-------------------------------------------------------------------

 

[GS]

You can filter on -""- and remove/delete the rows that display, then
turn off the filter.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[naira]

;1611654']You can filter on -""- and remove/delete the rows tha
display, then turn off the filter.

Please review point no. 2 of my post.

My *_data_needs_to_be_updated_on_a_real_time_basis_*.
Applying and removing filters is not a practically acceptable solution

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[GS]

;1611654']You can filter on -""- and remove/delete the rows that
display, then turn off the filter.

Please review point no. 2 of my post.

My *_data_needs_to_be_updated_on_a_real_time_basis_*.
Applying and removing filters is not a practically acceptable
solution.


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+

Good point! I did miss that...

The only means Excel makes available to us is worksheet events. This,
of course, requires VBA which violates Point1. I'm afraid that only
leaves 'magic' which I know you won't find anywhere and so...

Good luck!<g>

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion