Search by text option in excel 2010 using macro [SOLVED]

[pkd37223]

Hi, Can you please help me in adding "search by text" option in excel 2010 using VBA code. The basic idea is - with this "Search by text" option user can search the tool using specific "key word". The data is present in one excel sheet and the out put of the search should appear in another excel sheet. Please note that key words are not pre-defined/fixed.


I have also explained the requirement in-detail in the excel file uploaded in the below link.


https://docs.google.com/file/d/0B_cFem0ElVX1cnZaZ0NGWnR3X2M/edit?usp=sharing


Thanks in advance for your help on this.


Regards, Phani

 

[Hui]

First remove the text box from the 'Search page' sheet


Put a Black Border around H7:K10 the yellow area


Then in Output!D3: put

=INDEX('Data sheet'!D4:D7,MATCH("*"&'Search page'!H7&"*",'Data sheet'!D4:D7,0))


No VBA Required

 

[pkd37223]

Thanks for your reply.


I have tried the above mentioned formulae, however, If the "key word"is present multiple times in the "data sheet", the output is not coming properly. i.e. the same sentence has displayed multiple times. Please find the excel file in the below link with the 'data' and 'output'.


It would be great if you could suggest any solution for this.

https://docs.google.com/file/d/0B_cFem0ElVX1N0VTMDNtRlktUjg/edit?usp=sharing


Regards, Phani

 

[shrivallabha]

Try following formula [CTRL + SHIFT + ENTER and not just ENTER]:


=IFERROR(INDEX('Data sheet'!$B$1:$B$15,SMALL(IF(ISNUMBER(SEARCH('Search page'!$H$7,'Data sheet'!$B$1:$B$15,1)),ROW('Data sheet'!$B$1:$B$15)),ROWS($A$1:$A1))),"")


and then copy down until you get blanks.

 

[pkd37223]

Excellent. This is working fine. Thanks a lot shrivallabha.