+ Reply to Thread
Results 1 to 21 of 21

How do I create a search engine within an excel spreadsheet?

  1. #1
    Registered User
    Join Date
    10-04-2012
    Location
    bristol
    MS-Off Ver
    Excel 2010
    Posts
    4

    How do I create a search engine within an excel spreadsheet?

    I have created a spreadsheet which includes addresses, post codes and other information.
    I want to be able to type in a part of a post code and for all of the post codes that include that part of the post code to be displayed as well as the other information that's on the same row as that post code.
    I am completely new to excel and so don't know how to use formulas or functions. At the moment I am just filtering to do this but its very time consuming.
    e.g. From the segment of my spreadsheet below I want to be able to type in the first part of a post code BA1/ BA1 1 and for only post codes including that part of the post codes would then be displayed in a list.

    List Description Day Site_PCode Site_Name Site_Addr1
    280 KX05UWF Saturday BA1 5TJ HARE AND HOUNDS (LANSDOWN) LANSDOWN ROAD
    280 KX05UWF Saturday BA1 2LS TOPLAND (ROYAL CRESCENT HOTEL) 15-16 ROYAL CRESCENT HOTEL
    280 KX05UWF Saturday BA1 2LS TOPLAND (ROYAL CRESCENT HOTEL) 15-16 ROYAL CRESCENT HOTEL
    280 KX05UWF Saturday BA1 1LP THE MAD HATTERS TEA PARTY 5 ORANGE GROVE
    284 HX05KYB Tuesday BS31 1TN THE CO-OPERATIVE FOODS (PETROL STATION) PETROL STATION BATH ROAD
    284 HX05KYB Tuesday BS31 3DJ DAY LEWIS PLC 374 BATH ROAD SALTFORD
    284 HX05KYB Tuesday BA2 9ES FIRST BUS - WESTON ISLAND LOWER BRISTOL ROAD
    284 HX05KYB Tuesday BA2 3ED UNITE GROUP WATERSIDE COURT LOWER BRISTOL ROAD


    Type in BA1 1 and get the list as below.
    280 KX05UWF Saturday BA1 1LP THE MAD HATTERS TEA PARTY 5 ORANGE GROVE

    Type in BA2 and get the list as below.
    284 HX05KYB Tuesday BA2 9ES FIRST BUS - WESTON ISLAND LOWER BRISTOL ROAD
    284 HX05KYB Tuesday BA2 3ED UNITE GROUP WATERSIDE COURT LOWER BRISTOL ROAD

    How do I ask excel to do this which is not as time consuming as filtering. I am mainly after a front page search bar to be able to search in this way within the spread sheet easily and quickly over multiple pages.
    Many thanks
    Neil
    Last edited by wickets1210; 10-08-2012 at 02:11 AM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: How do I create a search engine within an excel spreadsheet?

    Post a sample workbook and I'll set it up for you in another sheet - the FAQ describes how you can attach workbooks, basically click Go Advanced while you are writing a post, then either use the paperclip icon or click on Manage Attachments and follow the steps.

    Pete

  3. #3
    Registered User
    Join Date
    10-04-2012
    Location
    bristol
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: How do I create a search engine within an excel spreadsheet?

    Many thanks Pete, your a legend.
    Attached is a sample work book as requested and I have shown where I would like the search engine to go if possible. On the actual work sheet each tab has around 3000 rows of data to search through. I need to be able to update the spread sheet once a month with new or different data.
    Any questions or if you need any thing else from me then please get back to me.

    Many thanks again
    Neil
    Attached Files Attached Files

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: How do I create a search engine within an excel spreadsheet?

    You have data in several sheets. Do you want to be able to pick up data from several sheets at once, or from just one? If the latter, does that mean that you want to select which sheet, and then enter the partial postcode?

    Pete

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: How do I create a search engine within an excel spreadsheet?

    Well, I went ahead anyway ...

    In the attached workbook you will see a new sheet which I've called Search, and on this there are two green cells - in H1 you can select a single sheet to look at using the drop-down (or all sheets if H1 is blank), and in J1 you can enter the fragment of a postcode, eg BS1, or leave it blank to get all postcodes. The data displayed will automatically adjust according to these settings.

    That's about all there is to describe, really. The helper columns (A in all sheets and B in the Search sheet) can be hidden if you want the sheets to look as they did before, and if you have a lot more data in your real file then you must ensure that the formulae are copied beyond each last row (further, to allow for new data). Let me know if you need explanations of what the formulae do or how they work.

    Hope this helps.

    Pete
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor Blake 7's Avatar
    Join Date
    10-01-2010
    Location
    Bogota, Colombia
    MS-Off Ver
    Excel 2010 64 bit and Excel 2007,
    Posts
    1,377

    Re: How do I create a search engine within an excel spreadsheet?

    pete -- that is awesome. Great post dude.
    Blake 7

    If your question has been answered, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

    If any member's response has helped to solve your problem, please consider using the scales icon on the top right of their post to show your appreciation.

    http://failblog.org/

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: How do I create a search engine within an excel spreadsheet?

    Quote Originally Posted by Blake 7 View Post
    pete -- that is awesome. Great post dude.
    Hey thanks, nice to be appreciated.

    When I responded to the first post I'd missed the last sentence - " ... over multiple pages". I might not have volunteered so readily if I had read that more thoroughly as it took me ages to set it all up, but, I had given a committment so I had to see it through. I'll have a fag-break now!

    Cheers,

    Pete

  8. #8
    Valued Forum Contributor Blake 7's Avatar
    Join Date
    10-01-2010
    Location
    Bogota, Colombia
    MS-Off Ver
    Excel 2010 64 bit and Excel 2007,
    Posts
    1,377

    Re: How do I create a search engine within an excel spreadsheet?

    lol - i'm anti smoking (even after the wild thing) but knock yourself out! you deserve it. I have copied your spreadsheet into my excel sample folder. The folder that i draw on every day, either at work or on the boards. One day it will come in handy! I'll pay you royalties!.

  9. #9
    Registered User
    Join Date
    10-04-2012
    Location
    bristol
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: How do I create a search engine within an excel spreadsheet?

    Many thanks Pete, the spreadsheet is great! I have added in all of the data now and it works a treat!!
    I will use it on a daily basis for work so you have really helped me out.
    I owe you a thank you drink!!
    Many thanks again,
    Neil
    Attached Images Attached Images

  10. #10
    Registered User
    Join Date
    10-04-2012
    Location
    bristol
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: How do I create a search engine within an excel spreadsheet?

    OHH NOOOO,
    help once again pete, i have opend this fantastic spread sheet up at work and it looks like am running an older version of Excel and i get the horrific massage.....

    This file was created in a newer version of Microsoft excel. The file has been converted to a format you can work With, but the following issues were encountered the file has been opened in read-only mode to protect the original File.

    -one or more functions in this workbook are not available in this version of Excel. When recalculated, these functions Will return a #NAME? error instead of returning calculated results......

    i think i am running microsoft excel 2003 but when i save some thing it says
    save file type as excel 2007.

    dont want to be checky but any more help would be great.

  11. #11
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: How do I create a search engine within an excel spreadsheet?

    No problem - just a slight change to the formula in column A of the Search sheet, as I had used IFERROR in there. I've made the adjustments and saved it as an XLS file. You will need to copy all your data into the five sheets and copy down the formulae in columns A of those sheets and column B of the search sheet.

    Hope this helps.

    Pete
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    01-07-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    64

    Re: How do I create a search engine within an excel spreadsheet?

    Just a quick question: If I didn't have to sift through the worksheets (as in I have one 1 worksheet), how would I go about implementing a search engine. I looked at the formulas you have set up and was wondering exactly how it worked! Thanks!

  13. #13
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: How do I create a search engine within an excel spreadsheet?

    src16,

    Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  14. #14
    Registered User
    Join Date
    01-08-2013
    Location
    Sofia, Bulgaria
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: How do I create a search engine within an excel spreadsheet?

    hi, this is awesome. Pete, would you explane the method of creating this table. Thanks

  15. #15
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: How do I create a search engine within an excel spreadsheet?

    There are 5 data sheets with identical layouts. In these I have used column A as a helper column. Cell A1 contains the sheet name and A2 in all but the first sheet contains a formula to find the largest number used in column A of the previous sheet (in the first sheet this is set to zero). Cell A2 thus represents the starting point for the numbering which occurs in the rest of the column.

    The formula in A4 of those sheets is the same (and is copied down to row 200 in the file that I posted), and this sets up a simple unique numbering sequence which follows on into subsequent sheets if a single sheet has not been selected on the Search sheet. The formula allocates a unique number only if the criteria on the Search sheet is satisfied, i.e. that the sheet is the one selected (or blank), and that the fragment of postcode on the Search sheet matches the postcode for each record. These unique numbers can then be used to retrieve each record that matches the criteria in the Search sheet.

    On the Search sheet I've used two helper columns (A and B), as well as a small table in columns T and U which finds the largest number used on each sheet. This table can thus be used to determine which sheet the matching data can be found on (in column B), and column A determines the actual row within the sheet where each matching record can be found. The bulk of the data can then be retrieved using an INDEX formula in columns C to K, and the formulae on this sheet has been copied down to row 100 as an example.

    So in summary, each record which matches the criteria is uniquely identified, so that the unique reference can be re-generated in the Search sheet and the matching records can be retrieved - this is the basic requirement for this type of problem.

    Hope this helps.

    Pete

  16. #16
    Registered User
    Join Date
    01-08-2013
    Location
    Sofia, Bulgaria
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: How do I create a search engine within an excel spreadsheet?

    Very helpfull. Thanks

  17. #17
    Registered User
    Join Date
    08-08-2005
    Posts
    76

    Re: How do I create a search engine within an excel spreadsheet?

    Hi Pete,

    Thank you for this spreadsheet, this is very useful. I have one question, is it possible to not display any information without the fields (sheet Cell H1 & Postcode fragment Cell J1) being filled in?

  18. #18
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: How do I create a search engine within an excel spreadsheet?

    The answer is Yes, but please read Arlette's post #13 - you should start your own thread with a link back to this one, rather than hijack someone else's thread.

    Pete

  19. #19
    Registered User
    Join Date
    12-09-2014
    Location
    Houston, TX
    MS-Off Ver
    2010
    Posts
    1

    Re: How do I create a search engine within an excel spreadsheet?

    this is amazing Pete. Thanks.

  20. #20
    Registered User
    Join Date
    01-28-2015
    Location
    Cornwall, England
    MS-Off Ver
    2010
    Posts
    4

    Re: How do I create a search engine within an excel spreadsheet?

    Hi there,

    I realise this was a while ago now. But I am trying to do the same thing in my Excel spreadsheet now. If possible could you describe how to produce the search functionality you created for Wickets1210? I have a number of different worksheets and hope to have a search page where a search term can be entered and a list will be populated below, including the rest of the row where that particular search term was found? The option to search only within one selected worksheet is not mandatory but would be great as an extra function.

    Thanks in advance for any help.

    Becky

  21. #21
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: How do I create a search engine within an excel spreadsheet?

    Hi Becky,

    there have been a number of references in this thread to Forum Rule 02, which states that you should not hijack someone else's thread (Forum Rules are available to view at the top of the screen). Instead, you should start your own thread, with a link back to this thread if it is relevant.

    I would also suggest that you attach a sample workbook in that new thread, which shows how your data is laid out and includes a description, with examples, of what you want to achieve.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1