Search from a list

[hoomantt]

hi Dear Professors;

i have a list of data and want to make it a list that when i choose a alphabet like "a" from a dropdown list , in another list i can see all of datas that start with that alphabet . i want it with excel formulas and if this is impossible i want this in vb.

example : i have in column d these words : apple ; hello ; chandoo ; all ; ali ; zebra ; ...

and i want to select "A" in dropdown list in cell b1 and can see in column b the words in column d that starts with a : apple ; all ; ali (every word in a cell not together)

thanks a lot.....!!!!!!!!!!!

 

[SirJB7]

Hi, hoomantt!


If you can afford to use a helper column just before column D (in the example C), you could try this:

-----

[pre]

Col.B	Col.C	Col.D
A		Full list
apple	1	apple
all	1	hello
ali	1	chandoo
2	all
3	ali
3	zebra

[/pre]
-----


B1 : cell with data validation against list of the letters of the alphabet (any case)

D2:D7 : full list of values

C2:C7 : helper column, formula at C2 and copy down, =CONTAR.SI(D$2:D2;B$1&"*") -----> in english: =COUNTIF(D$2:D2,B$1&"*")

B2:B7 : filtered list, formula at B2 and copy down, =SI.ERROR(BUSCARV(FILA()-1;C$2:D$7;2;FALSO);"") -----> in english: =IFERROR(VLOOKUP(ROW()-1,C$2:D$7,2,FALSE),"")


Link to sample file:

https://dl.dropboxusercontent.com/u/60558749/Search%20from%20a%20list%20%28for%20hoomantt%20at%20chandoo.org%29.xlsx


Regards!