Skip Blank and Duplicates in Dropdown Data

[Mehmud Khan]

Hi,

Could any one help in this please....
While choosing data from drop-down, duplicates and Blanks cell also reflecting.

Is there any Private VBA codes to avoid Blank and Duplicate Data in drop-down.
Please refer the attached file..

Thanks and Regards.
Mehmud Khan

 

[azumi]

Hope this helps

 

[bosco_yip]

Another option,

1] In E3, copied down :

=IFERROR(INDEX(D$3:D$13,MATCH(0,INDEX(COUNTIF(E$2:E2,D$3:D$13&""),0),0)),"")

2] A3>> Data validation >>
Allow : List
Source : =OFFSET($E$3,,,COUNTIF($E:$E,"?*"))
>>OK, and copied down

Regards
Bosco

 

[Mehmud Khan]

Azumi and Bosco....
thanks a lot to both for quick response.....

Your answers are really appreciable....

But is it possible to get the same solution without adding or overwriting another coloum... (like you both did in coloum "E"...)

Is it possible through some VBA codes ??

 

[Mehmud Khan]

Hello there,

The solutions given by you is awsm...
But when I implemented on my actual sheet then I faces lots of problems...

Could anyone put the formulas in the attached sheet...
Please refer the main attached file..

Thanks in advance....