Home |
Search |
Today's Posts |
#1
|
|||
|
|||
need help in fine-tuning a formula that removes blank cells in a column range
hi!
I need help in fine tuning a formula that removes blank cells from a 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(MA TCH($A$2:$A$52,$B$1:$B1,1)),0),0))) Problem is that each value is appearing uniquely in the result even though they might be appearing multiple times in the original range. Any suggestions to make the values appear sequentially in the order they appear and multiple times after emoving blanks? Alternately, if someone has a better formula would appreciate if it can be shared. Following limitations may please be considered: 1. No VBA/ Macros please since they are volatile and tend to disable undo option. 2. No array formulas please (those committed with CTRL+SHIFT+ENTER), since my current solution already uses array formula and that has considerably slowed down calculations. My actual worksheet has about 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 a formula and the cells are blank since the result of their calculation is blank. Sample file attached. Regards, Naira Last edited by naira : May 7th 13 at 03:53 PM |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help in fine-tuning a formula that removes blank cells in a column range
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 |
#3
|
|||
|
|||
Quote:
My data needs to be updated on a real time basis. Applying and removing filters is not a practically acceptable solution. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help in fine-tuning a formula that removes blank cells in a column range
'GS[_2_ Wrote:
;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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help fine tuning delete macro | Excel Programming | |||
Fine Tuning | Excel Programming | |||
help w/ fine tuning | New Users to Excel | |||
Fine tuning if statement.... | Excel Programming | |||
Fine-tuning selection change event for merged cells & wrap text | Excel Programming |