• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic Search bar in excel from partial input...

kuldeepjainesl

kuldeepjainesl

Member
Hi,


I have large database of customer name (say 2000-4000). I need a cell where i can start puting the partial name of customer and it pull up the list of those customer.


For example:


Form the list below when i start writing "ISO", in a cell and List will shrink and show the values having "ISO" in it. In this case this list will contain 4th row and 6th row.


PARIMAL ENTERPRISE

POLYCOAT ELECTRA SERVICES (INDIA) PVT LIMITED

AUTOMATION & GENERAL ELECTRIC CO.

ISOTECH ENGINEERS PVT. LTD.

JYOTITECH INDUSTRIES

ISOTECH ENGINEERS

SIEMENS LIMITED


I have read this artical somewhere at chnadoo.org but now i could not find it now when i am in need.


As of now , i am using filter/advance filter for this as of now but it keep me irritating as the list is very long.


Regards,

Kuldeep
 
Hi Kuldeep ,


Try the following formula , entered as an array formula ( using CTRL SHIFT ENTER ) in column C , from C1 downwards :


=IFERROR(INDEX($A$1:$A$7,SMALL(IF(IFERROR(SEARCH($B$1,$A$1:$A$7),0)>0,ROW($A$1:$A$7)),ROW(A1))),"")


Copy it down as much as you want to.


This assumes your list is in column A , in the range A1:A7. I am also assuming that the list of characters to be searched for , is entered in B1.


Narayan
 
Hi NARAYANK991,


Great....That's what i wanted. Just want to know, that if results can be filtered with each alphabet entered in B1 without having to press enter in B1. I am OK with UDF or macro if that can enhance this solution.


Regards,

Kuldeep
 
Hi Kuldeep ,


That can only be done using VBA.


However , I don't think it can be done with a UDF or macro alone ; the moment you start entering anything in a worksheet cell , Excel enters EDIT mode , and nothing else can happen till you press ENTER or move the cursor to another cell.


What you want can be done using a USER FORM , so that all data entry is under the control of a VBA procedure.


Narayan
 
It means if we use a user form in place of entering text in B1, this is possible. Can you guide me to do this.


As i remember i read it somewhere on chandoo.org itself but could not able to find it now. Do you have any idea of it.


I am thankful to you to suggest all these to me.


Thanks

Kuldeep
 
Looks like a combo box might be enough to help you out:

http://www.excelforum.com/excel-general/581510-auto-complete-for-dropdown-lists.html
 
Hi Luke M.


Problem to deal with is similar but i could not get the clue for its implementation. The property which is referred in that post is "MatchEntry property to: fmMatchEntryComplete" which is under ActiveX combo box. Sorry i did not worked on activeX form controls.


Seems, i will do my job with the solution given by NARAYANK991.


Anyway Thanks Luke M & NARAYANK991 for your valuable suggestions.


Regards,

Kuldeep
 
Well, go with whatever works best for you. Better to use something you can understand than something you don't. If Narayan's idea does not help, I would recommend you look at Debra's details instructions:

http://www.contextures.com/xlDataVal10.html
 
Hi Luke M,


Thanks for guide to land on that page. After all reading and playing, my observation is that this only gives you one best match for the input string....Yet drop down menu show the all values in original list. Good but can be used only when you have very good data input (without spelling mistakes) without any mistakes.


For example List has two values "Kuldeep" Kuldeep Jain" above method capture Kuldeep as soon i a press "K" but "Kuldeep Jain" will only be available after full entry of "Kuldeep+ space". and even drop down show the total list (2000 values) in place of having list of values carrying alphabet "K"


What i was imaging that drop down list will only carry the results achieved by the method of NarayanK991 via =IFERROR(INDEX($A$1:$A$7,SMALL(IF(IFERROR(SEARCH($B$1,$A$1:$A$7),0)>0,ROW($A$1:$A$7)),ROW(A1))),"")


Thanks

Kuldeep
 
Hi NarayanK anf Luke M.


It seems to me the basic to moderate level of requirement of this problem is solved by the above formula.


Now beside moving in the direction to make it real time dynamic, Can you suggest a change in this formula so that it can search only the values starting with the input string.

As of now "Search" function in above formula search the string at each level and return for every match.


To explain it


PARIMAL ENTERPRISE

POLYCOAT ELECTRA SERVICES (INDIA) PVT LIMITED

AUTOMATION & GENERAL ELECTRIC CO.

LIMTECH ENGINEERS PVT. LTD.

JYOTITECH INDUSTRIES

ISOTECH ENGINEERS

SIEMENS LIMITED


If i search "LIM", outcome should be only the value of 4th row and not the 4th and 7th Row. This i came across when i used your formula to a large database.


After collecting all pieces of formulas from you generous guys , i will implement these in a database of almost 125,000 Rows of data.


Regards,

Kuldeep
 
Hi Kuldeep ,


I was writing the VBA code to cater to your requirement of having a list based on each keypress.


Do you need this ?


Narayan
 
Sure NarayanK,


What else i may expect for. I am really thrill to get that code and use in my project. In case my both expectation can be embed in this, that will make it out of the world.


1) Return the values those are having that string.

2) Return the values those are starting with that string.


I express my gratitude towards you even for the your word that you are trying to write while you dont have right now.


Regards,

Kuldeep
 
So kind of you NarayanK,


Just to add in my last comment that I agree that both condition are separate and may need to configure by user in your code or may need to set a value in any cell to provoke the different set of codes.


1) Return the values those are having that string.

2) Return the values those are starting with that string.


Regards,

Kuldeep
 
Hi Kuldeep ,


Can you check out the file at this link ?


https://docs.google.com/open?id=0B0KMpuzr3MTVeDQwYzFLWnBlSDA


You will have to download the file and open it in Excel.


Narayan
 
Hi Kuldeep ,


Download from here , since I've removed a few unnecessary lines of code.


https://docs.google.com/open?id=0B0KMpuzr3MTVNEVVRm0xWXU3bzQ


Narayan
 
hi, i was on tour from last two days, so could not chevk it. i will give it a try comming morning and let you know & i hope you will make me WOW
 
Thanks NarayanK,


I am happy to get this file. It worked...


I am sorry but i could not find the clue where to configure the below mentioned statements.


1) Return the values those are having that string.

2) Return the values those are starting with that string. (Implementation in you code)


I will require the 1st as well in case their is mistake in data entry and may have slightly diffrent splling.


Regards,

Kuldeep
 
Hi,


A bit more help....


Initially you suggested a array formula to get the list.


=IFERROR(INDEX($A$1:$A$7,SMALL(IF(IFERROR(SEARCH($B$1,$A$1:$A$7),0)>0,ROW($A$1:$A$7)),ROW(A1))),"")


What change in this is required to make it compatible with other condition (Return value for name start with input string)?


I hope to get it till i made aesthetical chnages to the user form provided in your excel file.


Regards,

Kuldeep
 
Hi Kuldeep ,


Try this :


=IFERROR(INDEX($A$1:$A$7,SMALL(IF(LEFT($A$1:$A$7,LEN($B$1))=$B$1,ROW($A$1:$A$7)),ROW(A1))),"")


Narayan
 
You must log in or register to reply here.
Back
Top