Excel Query

Hi all,

Possibly not the best place for this here but I'll give it a try!

I have a large Excel spreadsheet that I use for my stock book for our little EBay business. It lists what we have paid for tools and what they sell for, works out all the costs of sales, Ebay fees, postage etc

It is about 20,000 lines long, stuff added every day or so.

We are always using the Find dialog to search for items, usually by text content & fill colour, ie RED for for sale, GREEN for sold pending payment plus colours for vaious auctions, suppliers etc.

This works very well for what we need, most of the time.

What I would like to know is if there is any way in excel FIND that one can search within the find dialog?

For example if I FIND 202 items that have Barnsley, is there a way I can search those for the one that also have, say 'hammer' as well while staying in the find list just generated? Kind of Find within find?

I cannot make the whole sheet a database as it is constantly changing, lines are always getting added, inserted or deleted.

Cheers, Martin

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

It;s been a long time since i used excel like that but you should be able to make conditional searches using 'IF' & 'AND' over ranges of cells.

You should also be able to run a datasort on more than one column to end up with a short list of your criteria.

<<I cannot make the whole sheet a database as it is constantly changing, lines are always getting added, inserted or deleted.>>

Not sure i understand that statement - it's what databases do. But i must admit that my foray into developing my own complex databases was finally abandoned using access - the front end was always in the way.

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.

Been a long while since I've used Excel but wouldn't the COUNT IF function do what you need? Should be no gap between count and if but auto complete keeps changing it !!!!

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

Hi Martin King 2

Not sure about Excel But if you Also have Microsoft Access with your version of office there is another way.

I use an older version of access (2003) it will work on later versions too, you may have a different menu system.

1. Open a new Access database.

2. Now save it to give it a name

3. At the top of the page you will see the file menu.

4. Within the file menu you will see "get external data"

5. Having selected get external data select "link tables"

6. It will open a window named Link..... Find your Excel spread sheet in your computer and select it.

7. Assuming you only have one sheet (Worksheet) in your excel file select sheet 1

8. Select next

9. Ideally your sheet will contain one line for headings if it does tick first row contains Column headings. These headings will be used in your access view. This is the preferred option in the future.

10. If you have used more than one row ignore the tick and select next

11. Give your "Linked table" a name.

12. In the Access menu you should be in the "Tables" area if not select it.

Open your linked table!!

Ok So what have you done? The Excel spread sheet is still intact and in the same location as it was before. You have enabled Access to find open and if you wish edit it.

When you open your link you will be able to see your spread sheet (But what you are looking at is an access table representation of your spread sheet. It does not use formulas but the results of your excel formulas will be shown.

In answer to your request to be able to filter the data searching for subsets drilling down to the data you are after It is a lot more powerful than Excel; and it is all under your right mouse button.

Click on any column heading and you can sort the table up or down.

Note the "filter For:" in the right click menu. Type the word or number you are looking for in the small box and hit enter, Instead of seeing all your data you will only see rows of data that matches your search in that column. You can do the same in as many columns as you like until you get the list you want. you will get less and less rows.

Once you have the information you want you can select and copy the data into another spread sheet or just write it down.

When finished right click and select remove filter sort and you will see the entire set of data again. (Sorting here does not sort your spread sheet only the access table representation of it)

The "filter for" can search for part words, bits in the middle of words and many other "Criteria" If you find this method useful Consult the help system for examples. or here **LINK**

Caveat:

Just like your spread sheet if you actually change the values in the cells of the Access table view they will be changed in your spread sheet as well Remember you are linked to (your) spread sheet. So if you give this a try (And you will not regret it) make a copy of your spread sheet and test on that until you are familiar with the process.

This is only the tip of the iceberg There are hundreds of Access Self help books available on the net and dozens of public forums for help.

The process I have detailed above should take about one minute to do, the table will open in a flash.

Regards
John

mANY THANKS jOHN, i DO HAVE aCCESS BUT IT IS NOT INSTALLED, i WILL GIVE IT A GO ON A COPY OF MY FILE.

ops sorry about the caps lock.....

Martin

Don't make a mountain out of a molehill by messing with Access. Just get to grips with the dropdown filters. They are exactly what you are looking for and you will kick yourself for not realising they were there earlier. Takes seconds to set them up once you know how. Really powerful and you can select what you want quickly and easily.

Murray

.

+1 ... as it's Polling Day

MichaelG.

Access has no mountains in it. These are found in the human mind and yes they must be overcome.

To Boldly Go......

Regards
John

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

> decide on the underlying structure of your data before trying to define Access tables.

Which in one clause, explains why so many people use Excel instead of Access.

Neil

(Who used to use access until he realised he was using it because he enjoyed the challenge and not because the end result was better than Excel combined with judicious use of pivot tables).