• 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.

Search found

Istiyak

Istiyak

Member
Greetings.! Can we search the similar records with the help of any formula. If we enter john in cell a3 so in search cells (a5 to last cell depends upon the results of entered value) we can get similar values of john. I.e. John smith, john paul, john ntan etc.... Which depends upon a lookup data 4m a second sheet.
 
Hi, Istiyak!

Supossing you have your data in column A, from A5 to A32, select this range, go to conditional format and type this condition:

=NO(ESERROR(ENCONTRAR(A$3;A5))) -----> in english: =NOT(ISERROR(FIND(A$3,A5)))

Then choose the appropiate format, apply to range $A$5:$A$32 and check it.

Regards!
 
Sirjb7 thnx 4 response. Actually i want to creat search tool:-q in which user jst able to see result after entering data in search box. User should not aware abt database. Search space should blank when user enter value then only result should appear. I dnt want to highlight records. Hope u understood what i mean to say:-q

regards
 
HI, Istiyak!

Just coming back from Sunday's outdoor activities. Tomorrow see in detail.

Regards!
 
Hi, Istiyak!

Can you please describe in detail where is your data stored (workbooks, worksheets, ranges, ...)? Better if you can upload a test file. Thanks.

Regards!
 
Hi Sirbj7,,,, Thanx for your passion..


I have uploaded a file on my web site. Path is below

http://istiyakshaikh.hpage.com > Downloads > Search found.xls


Any query plz let me know.


Regards

!$T!
 
Hi, Istiyak!

I re-uploaded an approach to your solution to:

http://www.2shared.com/document/5feOHgcO/search_found__for_Istiyak_at_c.html

It uses named ranges for each data type (input, output, search, message, result), just one formula in L5 for the search count, the event Worksheet_Change just for cell L3 search argument (that clears the output & message areas and fills the output list with the results), an output list from K6:K25, and an overflow message at K26.

Behind that you'll have an array named sList (sized iLists). In the example there are displayed only the first (output area sized) matchs. You can upgrade it adding command buttons for first/previousnext/last groups of (output area sized) elements, or show them as a listbox, or... lots of manners.

Good luck.

Regards!
 
Hi,,, SirJB7 Thanks A lot for my solution its working bt i think there is some problem in that. when search match with exact value it shows that only value in search List however it has to show all the related search. Just try with Only entering "John" in Record and then search.

Another thing i m not very mch profession in VBA understanding.Where is iList and sList i dont understand it. If possible then plz provide the solution with Formula..

Thanks For ur Response.

Regards

!$T!
 
Hi, Istiyak!

I don't fully understand your issue: I tried entering "john" in the file I uploaded and I retrieve 3 values, "nathn", "smith" and "emile"; I tried entering "SirJB7" that is an exact match, and I retrieve 22 values (19 shown).

Sorry not providing you a formula solution, I chose the VBA alternative because of the excess of retrieval cases for a given argument (i.e. "SirJB7" shows 19 from 22, but ir you enter "John" to search on real data... I guess you'll have much more.

That's why I told you it was a first approach to a solution, and I pointed to use the sList array and the iLists counter, that are embedded within the VBA code, and might be used adding a pair of command buttons to the real workbook, so as to navigate thru the total number or matches if it's greater than the output area destinated for displaying.

It has many pros, and just a little con: you'll have to handle with VBA code and controls. It isn't too much difficult but it isn't too much easier, it just depends on your familiarity with programming. I haven't the time for guiding you thru the learning process, but if you wish, send me the real workbook, with the detailed requirements and I'll try to adequate the first approach to the final solution.

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