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.

Problem is that the formula I found somehow tends to output the result 3 times irrespective of the value's actual presence in the range and sorts in the sequence of the value's first appearance in the range.

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.

Edit:

Have uploaded another file called sample file_new.
It ALMOST contains the solution I need. Only problem is that each value is now appearing uniquely. Any suggestions to make the values appear sequentially in the order they appear and multiple times?

Regards,

Naira

in c2 put
=IF(A2="","",MAX($C$1:C1)+1) fill down
in D2 put
=IF(ROWS($A$1:A1)>COUNT($C$2:$C$500),"",INDEX($A$2:$A$500,MATCH(SMALL($C$2:$C$500,ROWS($A$1:A1)),$C$2:$C$500,0)))
fill down

The solution given by Martin works properly, but those in search of an even cleaner and faster solution would probably find the solution given on this link even better: http://www.excelforum.com/excel-form...uplicates.html

what are you talking about?

if you already HAVE a solution to this question, why bother to post it again and waste members time???

Welcome to the Forum, unfortunately:

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

http://www.excelforum.com/excel-form...uplicates.html

Thread Closed.