Creating list from data with blank spaces and also eliminating a repeating value

Not exactly clear to me where you want the results but it would make sense to make use of the AutoFilter capabilities XL has to offer, in VBA terms and based on your sample file:

Please Login or Register  to view this content.

The above when run should provide the listing in F1 onwards based on content in A.

You could replicate the above with formulae but it would not be the best approach IMO.

Another approach. This one is w/o VBA code, using array formula.

Please Login or Register  to view this content.

Vusal, if interested, you could shorten that slightly:

Please Login or Register  to view this content.

You could also add a LOOKUP hander for the errors rather than letting the function return an error and using Conditional Formatting to hide them, see additions in red:

Please Login or Register  to view this content.

DonkeyOte
Thanks for revising. I'll research them...

DonkeyOte
Thanks...
Everything is clear but REPT()
Can u please explain what this function is supposed to do here.
I understood like it returns 255 pt of Z letters????

PS: I'm just sudying....I aint expert as you... but I'll become, sooner or later..

Yes, REPT("Z",255), creates a string of 255 Z's... ie to represent a "large" text string such that the LOOKUP returns the last text string in the 2 value Array... if the INDEX returns an Error the LOOKUP will return the Null, else it will return the output of the INDEX.

If the INDEX were returning numerics and you wanted to retun 0 instead of error you would use

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,INDEX(...)))

ie ensure that the criteria > possible outcomes such that the LOOKUP returns the last value of same data type as criteria (errors are ignored)

And I am no expert, I'm still learning myself.