search engine in excel

valve type diameter price brand
1) pneumatic butterfly 500 3200 AA
2) hydraulic butterfly 600 3000 BB

Above is the example of the list of price comparison for valves where the information in every row will be random and will not be categorized accordingly.

How can i make a search engine where i key in the information for "valve', "type" and "diameter", it will list out all the prices and brands with the same information that i key in earlier.

for example- when i key in "pneumatic, butterfly and diameter", i will get the list of the prices and brands.

Thanks a lot

........valve.....type..diameter..price..brand
1) pneumatic butterfly 500 3200 AA
2) hydraulic butterfly 600 3000 BB

Above is the example of the list of price comparison for valves where the information in every row will be random and will not be categorized accordingly.

How can i make a search engine where i key in the information for "valve', "type" and "diameter", it will list out all the prices and brands with the same information that i key in earlier.

for example- when i key in "pneumatic, butterfly and diameter", i will get the list of the prices and brands.

Thanks a lot

How about Autofilter?

Threads merged. Please don't post the same question twice.

sorry i thought i haven't posted the thread.

by using the advance filter i manage to filter the range and copy to another location.

but how can i make the filtered range automatically filter when i alter the values in the criteria range instead of repeating the steps?

Hi

Will you always be using all 3 criteria to make your selections? Or could you be using 1 or any mix of the options?

rylo

i will be using 3 or more criteria to make my selection.

thanks for your reply .

how do i make a search engine in excel?

for example i will have a list of this table:

actuator type diameter price brand
1) pneumatic butterfly 500 3200 AA
2) hydraulic butterfly 600 3000 BB

i want to make a search engine where i key in 3 criteria which are the "actuator", "type" and "diameter", excel will filter the table and list out all the prices and brands with the same criteria in other columns

thanks a lot for your reply

Are you sure you need a "search engine"? The built in Excel Auto Filter Feature does basically what you need to do. Is there any reason it doesn't suit your needs?

Hi

Try this

In sheet1 A1:E3 enter your example data.

In sheet2!A1:E1 enter the same headings as sheet1!A1:E1.
In Sheet2!G1:K1 enter the same headings as sheet1!A1:E1

Right click on sheet2 tab, select view code and insert the code

Please Login or Register  to view this content.

Now if you make a change in the range G2:K2, the filter will output into columns A:E of sheet2.

HTH

rylo

thanks a lot rylo

halo,

i want to make the criteria range vertically instead of horizontally, for example :

actuator - pneumatic
type - butterfly
diameter - 500

while the filtered data generated is display horizontally :

actuator type diameter brand price
pneumatic butterfly 500 AA 6300


can anyone edit the code for me and add the sort ascending for the "price" column of the filtered data?

thanks a lot

Hi

Using the same setup as I provided earlier, add the headings in sheet2!M1:M5. You now put your selections into the range N1:N5. Change the code to

Please Login or Register  to view this content.

If that approach would work, then you could change your criteria selection positions, and the area that the filter uses as the criteria range to suit.


rylo

really appreciate your help. thanks again rylo

now i want to add a drop down list box at the cells of the criteria range, look like the one in autofilter where it will only list out options without repeating the same options base on the database sheet.

i have try to create a drop down list box and format the control but it will list out and repeat the options.

can anyone help me out with this? thanks

Hi

Sorry not sure I follow. Do you want an incell dropdown, that shows a list of the unique items for a particular column, that can be applied to the criteria section?

rylo

hi,

yeah i wan a drop down list like the one in autofilter where it will only list out the options without repeating.

thanks for your reply

Hi

Say your source data is in column B, and you want the output to go into G2 then try

Please Login or Register  to view this content.

rylo

sorry i am not sure what you mean.

my criteria range is A3:F4, and my output will shown on A:M at the same sheet, while the source data to be filtered is on sheet "database".

what else should i do after i paste the code.

sorry for being a noob

Hi

Post up an example workbook that reflects your working structure, some dummy data, and advise where things are to go, where you want your drop down etc so the code can be configured for your situation.

rylo

i have done that and put some sample data on column B, but i didn't see any drop down list or output.

what mistake have i made?

Hi

Zip your workbook, and attach it to your reply to this post so we can see your workings.


rylo

Hi Rylo,

this is the file.
i just what to make a drop down list like the one in autofilter on every column of the "search criteria" where it will list out the options from the "database" sheet.

therefore i can choose the options instead of typing the word.

u effort will be very much appreciated

Hi

In your sheet2 code area (underneath the worksheet_change event code) add the code below.

Please Login or Register  to view this content.

Now if you activate sheet database, then activate sheet search, go to cell A4 and you will see a dropdown that has the unique sorted list of items for selection.

You will have to expand the code and repeat for each of the 5 other criteria selection options, but you don't have to repeat the sortit function.

See how that goes.

rylo

thanks rylo. you are my hero!

one more request.
I want to make the options in the list of cell B4 according to the option selected in A4.

for ex :

option A - apple , orange
option B - apple

when i choose option B in cell A4, the list in cell B4 will only list apple instead of apple and orange.

Can you help me out with this?
thanks

Huh? Apple? Orange?

Have you put this on the right post? This one relates to the control valve issue.

Or do you mean that if you pick a control valve from the drop down in A4, then instead of getting a full list of options for the Valve Actuator, you will only get those options that are available for the control valve? If so, then you will have to fill all the missing cells in your database, and create a list of the items in Database!B where the entry in database!A is Control Valve.

However as these dropdowns are only filled when a sheet_activate event is done, you will have to change the trigger mechanism.

Can you be more detailed on what you want to happen for what activity.

rylo

hi rylo,

The apple and orange are just an example. Ya you are right, when i choose control valve in A4, instead of list out all the options in B4, it will only list out the options available for control valve.

So can i only use the code to make this happen instead of creating an extra sheet? If yes can you help me out with the code?

Thanks!

HI

OK, can you update your example file so that all the items are completed. This can then be used to create the specific list for the selected item in B4.

How far down do you want this to go? Restrict the entries across the other option boxes depending on the previously selected entries?


rylo

this is the updated file.

I want to make A4 filter the list the unique options in column A of the database, while B4 will depends on A4, C4 depends on B4 and so on.

thanks

Hi

This should get you going. The only problem at the moment I can see is that you have to work from left to right in terms of selection. If make a selection in A4, then try to make a selection in C4 without selecting something in B4, it will error out. I've put in an error trap that is pretty brutal and gives a message, but it will react to any error that is found, so in some cases it may give a false idea of what has gone wrong. See how it goes to and let me know.

rylo

hi rylo,

thank you for your help
i want to make it show the results when i choose either 1 of the options in the cells without choosing the option before it, so i use an extra sheet to make it happen and finally able to do it.

Once again, thank you very much for your help, really appreciate your hard work and the time spent on me