custom search function that list all the result in one place

[smatbis]

hello all

what i like to do is imitate the search function within excel but with slight enchancement

this i what i would like to achieve:

1. push a button in sheet DB , an input box will pop-up
2. key-in the keywords in the input box , it can be
words or numbers , just like the excel search functiion
3. the result will be appeardd in the RESULT sheet
4. below are few examples what i want to achieve:

example 1 ..if i keyin the keyword NEW , the result that appear in the RESULT sheet will be :

site id site name key details
1234 new york keso , master key
3765 new albany 8.1 , bm key

example 2 ..if i keyin the keyword 4343 , the result that appear in the RESULT sheet will be :
site id site name key details
4343 seattle site key

thanks in advance

PS: please refer to attachment for further understanding , tq again

[Pete_UK]

You can do this with a couple of formulae. In the result sheet I've left a gap for you to enter your search criteria in row 2 (the yellow cells).

I've used this formula in D2 of the DB sheet:

=IF(AND(A2<>"",A2=RESULT!$A$2)+AND(RESULT!$B$2<>"",COUNTIF(B2,"*"&RESULT!$B$2&"*"))+AND(RESULT!$C$2<>"",COUNTIF(C2,"*"&RESULT!$C$2&"*")),MAX(D$1:D1)+1,"-")

and this can be copied down as far as you need to - the hyphens indicate where the formula is active. This formula identifies the records which match the criteria and gives each a unique sequential number.

I used this formula in A4 of the Result sheet:

=IFERROR(INDEX(DB!A:A,MATCH(ROWS($1:1),DB!$D:$D,0)),"")

which is then copied across into B4:C4 and then copied down as far as is required (I've copied to row 12). You can see the result with "new" in B2.

Hope this helps.

Pete

[Richard Buttrey]

Here's another approach which uses a macro to filter the results

Just enter the key word in F1 on the db sheet. There seems little point in having a button and pop up input box since that's just adding to the number of keystrokes needed.

The Sheet Change macro is

Please Login or Register  to view this content.

[kev_]

Similar (to Richard Buttrey) but different
- uses standard data filter

[smatbis]

thanks to all posters for the great effort
unfortunately it doesnt do what i intend to do

my task is to create a search engine similar to "ms excel built in find and replace"

the difference is ::
i want to display a list of result based on my keyword in a different page , while if i use "find and replace" built in , i only can use it at the same sheet and it will scroll down or scroll up if the keyword make return

the search depends on the keyword , partially or whole , just like when you use find and replace in ms excel

so it will very helpful when user search in a larger list and want to display all the result , in a different sheet.

hope someone can help me achieving this, thank you again

[smatbis]

Similar (to Richard Buttrey) but different
- uses standard data filter

hello sir

at first i dont understand you work method

but after look carefully your work flow...finally i understand it

your method come very close to my requirement

but one thing occured

on the sample site id that i use in my ws example , it only contain number , which is 4 number

in my real data , my format is numbers and an letters...i.e C1234C or VB6723Z

the sample you give me works fine when my site id is in numbers

but when my site id contain letters , such as 1234C or 6723ZX... it return nothing

could you please help me again, thanks again


PS: please refer to attachment , tq


OTHER PROBLEM THAT I NOTICED...

1. i cannot use the keyword for example 1234 because it will return nothing
i want to be able just typing "123" and it will list all the 123 inside my Db , tq again

[kev_]

Amended as requested

[Richard Buttrey]

Re the #3 solution.
Change your column A Data to be text not numbers and then A3 on the Results sheet to be

="*"&DB!F1&"*"

[jindon]

Try

Please Login or Register  to view this content.

[Pete_UK]

You can change my first formula to this in D2 of the DB sheet:

=IF(AND(A2<>"",COUNTIF(A2,"*"&RESULT!$A$2&"*"))+AND(RESULT!$B$2<>"",COUNTIF(B2,"*"&RESULT!$B$2&"*"))+AND(RESULT!$C$2<>"",COUNTIF(C2,"*"&RESULT!$C$2&"*")),MAX(D$1:D1)+1,"-")

if your site ID values are text.

Hope this helps.

Pete

[smatbis]

Amended as requested

fantastic sir...well appreciated and tq

[smatbis]

Try

Please Login or Register  to view this content.

thanks sir

another awesome approach, thanks...

[smatbis]

You can change my first formula to this in D2 of the DB sheet:

=IF(AND(A2<>"",COUNTIF(A2,"*"&RESULT!$A$2&"*"))+AND(RESULT!$B$2<>"",COUNTIF(B2,"*"&RESULT!$B$2&"*"))+AND(RESULT!$C$2<>"",COUNTIF(C2,"*"&RESULT!$C$2&"*")),MAX(D$1:D1)+1,"-")

if your site ID values are text.

Hope this helps.

Pete

thanks sir for the effort