Why is this formula removing duplicates?

hi!

I have the following 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(MATCH($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 removing 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

The attached example should not slow your workbook down in any way as it maintains your "results". By adding a "key" column to index the cells that should be collected, and noting in another cell the maximum index key value, the formulas in the RESULTS column are extremely fast.

I've marked this thread as SOLVED for you.
Next time, select Thread Tools from the links above and mark this thread as SOLVED. Thanks.