If you cant "search within a search" you may be able to either group those terms into a single block or extract them to a separate page

If you can group them into a single block of records then you can sometimes search within that block in the document with the click-drag the mouse over the block option

Don't forget, use a backup/copy for any messing around, never never mess about with the original document in case of disaster

Edited By Ady1 on 07/05/2015 10:31:04

If you organise the data well then the Filter command will do what you need.

For instance, if the top row is made of header names then you can set all that row as individual filter elements.

Google it if you want to know more.

As capstan speaking says, filter buttons are way to go. First filter on Barnsley then filter on hammer – easy .

With a spreadsheet this size you should really be looking at Access, steep learning curve though if you have not used Access before.

Excel very easy to use but gets rather unwealdy to use as spreadsheet size increases.

A little tip for those trying to get to grips with Acess, work out what you want the database for, what you want to input and what you want to get out BEFORE you even open Access.

Access is NOT a program you can just dive into, it needs a lot of thought beforehand, but it will pay dividends in the long run.

Having said all that, everyone still dives straight in, wonders why Access seems so difficult and then reverts back to Excel  ……… 

Edited By Howard Winwood on 07/05/2015 12:33:33

.

+1 … as it's Polling Day

MichaelG.

I guess that most participants on this forum would hesitate to use a chisel as a screwdriver or a screwdriver as a pry bar; we would generally try to use the most appropriate tool available to us for the job. The same should be true in the Computing arena. The problem as described is a database problem rather than a spreadsheet problem. Hence Access is a more appropriate tool than Excel, especially as the data set grows. Using Excel as a data storage tool is not at all uncommon, indeed, before retiring I spent parts of the last 20 years trying to persuade researchers at the University I worked at that their data was ‘complex’ and that a database tool would in the longer term be more appropriate than a spreadsheet (or in some instances, a card index). I was not always successful

While it is easily possible to import the Excel data into Access as a single table the result would give little advantage to the user other than possibly the ability to allow more that one computer to concurrently update data. As noted by Howard, database products such as Access only really come into their own when data is grouped into logically coherent collections, each represented as its own table (in this example things like customers, suppliers, products, orders etc.). The power of database products like Access is you can easily create ‘views’ (called in Access-speak queries) which lets you see selected data from the underlying tables joined together to form a new logical result tables – for example:

give me all the customer contact details from all the orders for widget X received in the last 30 days that have yet to be fulfilled.

Such a query would involve customer, order and product tables.

While the result table from a query such as the one above need not physically exist, it can be used an input to further queries as if it did; Access will automatically materialise the data as required.

There is a learning overhead with products like Access and the initial concepts curve is a little steep but Access is actually extremely well designed in that the product is quite usable using the ‘drop and drag’ graphical interface but then like an onion there are successive layers that allow more and more complex applications to be designed as the user becomes more knowledgable; indeed, if you get as far as the programming language component (Access BASIC) you can build system of arbitrary complexity. However, to reiterate Howard's comments, decide on the underlying structure of your data before trying to define Access tables.

You mentioned that currently have around 20000 rows in Excel. I would suggest that is probably almost as far as you would really want to go within Excel and that you are rapidly approaching the point when considering alternative strategies will become necessary rather than just desirable. Indeed, looking to the future, Excel won’t cope well when you have 10 time as many rows whereas that is what database products are designed to do – and when your data expands 100 fold or if you want to put your data online, you can relatively easily replace the data storage component of Access by a grown-up brother such as SQL-Server.

Maybe its time to put down the screwdriver and start looking for the pry bar

Regards

Ed