Help please . . . Search Engine advice

[Nickh2203]

Hi,

I found a search engine in Excel at

http://www.excelforum.com/excel-gene...readsheet.html

. . . . and was wondering whether I could borrow some of it for a job I have at work. Pete_UK created a search engine for wickets1210 which looks like it might be able to do what I want.

I can understand the formulas involved (self taught) however, I think that there is one area which won't work as I want it to. To explain, I'm creating an Asset Database which lists all sorts of equipment and articles. There is no standard naming convention and I'm having to create some descriptions for some of the more exotic pieces of machinery we have.

In the first sheet after the "Search" sheet . . . TW, the formula in col A is

=IF(E4="","",IF(AND(OR(Search!$H$1="",Search!$H$1=$A$1),LEFT(E4,LEN(PC_frag))=PC_frag),MAX(A$2:A3)+1,""))

which works fine if the item to be searched for (in this case a Post Code) has a standard convention. The difficulty I have is setting up a search for a string which varies and may be separate words. I have experimented with LEFT, MID, RIGHT . . . substituting a string length etc, but nothing appears to produce the results I need. I want to reproduce the results of the search seen in Pete_UK's spreadsheet but with the flexibility of picking up all records containing the searched for item name e.g. Drill, Pillar Drill, Hand drill, 5 Axis Drill . . . etc.

Can anyone advise me whether this is possible ?

Thanks,

Nick

[Nickh2203]

Can no one help ?

[Pete_UK]

Hi,

I've just seen this thread. Are you still looking for a solution?

If so, it would help if you attached a sample workbook.

Pete

[Nickh2203]

Hi Pete,

Thanks for the reply . . . I do appreciate it . . . honest. Today is actually my last day here and I'm moving on.

I was on a 6 month contract and my contract has now ended, so I really have no need now for a search engine.

Just for info, what I was trying to do was make a couple of columns in several worksheets within a single workbook searchable. The database is an asset database which ranges from hand drills through industrial robots, to 11m x 5m autoclaves. I wanted to give the guys an input sheet which would search for various keywords or names, very like the one you produced using Post Codes and names. My problem was that I could not work out how to get the thing to search for key words of differing lengths and similar (misspelt) names.

That said, I would still like to know if it is possible. So I've cut down a copy of the workbook I've created for you to have a look at.

Thanks,

Nick

[Pete_UK]

Hi Nick,

Sorry to hear that your contract has come to an end - hopefully you will get another one soon.

I've set up a search function in the attached file, where I have added a Search sheet similar in operation to the previous file - in cell D1 (named range Desc) on this sheet you can enter any number of letters or words to describe the item you are looking for (or leave it blank to list everything), and you can also choose a single sheet from the drop-down in F1 (named range Sheet), or leave this blank to search all sheets. I've only copied the formulae down to row 30 and across to column N in the Search sheet, but you can copy them further if you wish, depending on the amount of data that you have or expect.

The main difference between this file and the earlier one is how the records are selected in the subsidiary sheets, where I have inserted and used column A as a helper as before. I have this formula in A2, which is coloured blue and copied down to row 500:

=IF(C2&D2="","-",IF(AND(OR(Sheet="",Sheet="Composite Manufacturing"),COUNTIF(C2:D2,"*"&Desc&"*")),MAX(A$1:A1)+1,"-"))

By using COUNTIF with wildcard characters either side of the Description, you can look for substrings, and the way this is set up it will search both column C and column D for the word(s) in Desc - if it is in either column, and if this is a valid sheet, then the count will be incremented. If Desc is empty then the wildcards will ensure that all records are included, so to avoid this happening when both C and D are empty, this is over-ruled in the first part of the formula.

The formula is very similar in the other sheet, although obviously the sheet name is different.

I hope this satisfies your curiosity - just type whatever you like in D1 of the Search sheet.

Pete

[Nickh2203]

Pete,

Wow !

That's exactly what I was hoping for . . . many thanks.

I was able to follow the formula's in the first workbook, but couldn't work out how to search for parts of a string. I shall have a go a understanding what you've created. This self teaching isn't easy when you get ancient !

Is it possible to carry the links to the photos' into the search results too ? . . . just wondering

Many thanks once again,

Nick

[Pete_UK]

Hi Nick,

glad you like it.

With the photos, this is difficult for me to test out, as I don't have the photos on my machine. I think what you have done is to embed a hyperlink to each photo in the main file, and as such this is not "data" to Excel and so it just brings a zero back to the Search sheet, whether there is a link to a photo or not. The best I can offer is to put this formula in F4 of the Search sheet:

=IF(OR($A4="-",$A4=""),"",HYPERLINK("#'"&$B4&"'!R"&$A4&"C5","photo"))

then copy down. Note that the HYPERLINK function is using R1C1 notation, so the "C5" means the 5th column (or column E), and "photo" is just the message that appears in the cell - you could have "view" or "jump", or whatever you like. If you click on "photo" it will take you to the appropriate cell in the appropriate sheet for that record, and so you could then click on the photo icon to view it - it does mean two clicks, but that's not too bad. One drawback is that there might not be a photo icon in that cell, but I can't see any way of detecting this and making the cell in the Search sheet to show blank in this case.

Anyway, hope you enjoy the Easter break, and if that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete