Multiple Searching a Data Set

Hi,

What you are asking for is more than achievable, even withough VBA. However, I notice only a dataset in your attached sheet, and no evidence of where, or in what dimensions, your desired returns would be.

Could you possibly re-attach with this in mind? It's easier to work out the necessary formulas when you have a set destination in mind.

Regards

Hi again,

Looking at what you've posted, I feel that you would be much better off posting this thread in the Access Tables & Databases section:

http://www.excelforum.com/access-tables-and-databases/

Please don't misunderstand me - this is possible in Excel; it's just that Access is far more appropriate for this. Of course, this depends if you actually have Access or, if not, are willing to invest in it.

If you still would prefer to go ahead in Excel - or if you get no response after posting in that section of the forum - please don't hesitate to come back.

Regards

Use the Auto Filter Icon on the Ribbon. I have engaged it in your file attached.

You can then filter on any category you wish.

Here is a short explanation of filtering. Keep in mind it was older versions, but the concept is the same

http://www.techonthenet.com/excel/filters/index.php

Actually I was thinking about this and was just wondering how flexible you were on the set-up that you gave in your attachment?

Specifically, are you wanting the search criteria to consist of these several user input boxes? The reason I ask is that you could achieve the same functionality (i.e. ability to search/filter the records on multiple criteria - in fact as many as you wish) by using the in-built auto-filter on the headers of your table. This would be a straightforward and simple solution, and remove the need for any complicated formulas in there.

The only potential drawback to this is that it would allow a user to view all data records - I'm not sure if this is something that you'd rather not be possible?

Regards

Yes, and this is why Access is much more preferable for this sort of thing.

For example, I imagine that you would like, as well as the ability to search using all - or at least many - of your criteria, the functionality that, once a user starts typing, e.g. the first three letters in a particular search box, that a drop-down list of possible returns is presented to them? This is something that is a common feature of databases (i.e. Access) but less easy to replicate in Excel.

Or perhaps you would settle for not having this luxury and, also, for a search facility which would tolerate exact matches only, perhaps presenting the user with a 'No matches found' message if this is violated? The drawback to this is the obvious loss of flexibility in searching and the added impetus on users to know precisely what they are looking for (it could become quite frustrating otherwise).

If you are willing to make these compromises, then perhaps we can work something out.

Regards

Can you clarify which of your variables you'd like to use as search criteria?

Also, which of these are going to be based on exact matches and which, if any, will be based on searching within a range, and what that range would encompass (e.g. searching for Age or Year Joined using a lower and upper bound)?

Regards

Sorry - you misunderstand. It's actually far easier in Excel if we do restrict it to exact matching, or at least partial matching.

Having it so that, e.g. if a user types in "Rob" and expects to see all entries returned for "Rob", "Robin", "Robert" or even "Nairobi" is not so much a problem, but inexact matching is not so straightforward, e.g. so that a search entry of "McGowan", if not found, would return "MacGowan" (if it existed).

If you're fine with the first option, you still need to decide which one you'd prefer (exact only or exact and partial) - you might decide that it's actually undesirable that all options containing "Rob" are returned, and force it to return an exact match for that string only.

Sorry for all the questions - hopefully there won't be too many more.

Regards

Sorry - confused. That doesn't really answer me.

"If you're fine with the first option, you still need to decide which one you'd prefer (exact only or exact and partial) - you might decide that it's actually undesirable that all options containing "Rob" are returned, and force it to return an exact match for that string only."

Regards

Ok, thanks. I should have everything I need to start working on it now. Be in touch shortly.

Regards

Honestly, using complicated array formulas to fetch results with that much data would just slow the workbook down to probable non-usability. I would have to stick with Alan's original suggestion of simply using the auto filter, which can do what you've described already. If there is some special need to not use the built in Filter functionality, then I would have to recommend using VBA to fetch the results. It can work with that much data easily and output the results as values in order to keep the sheet working quickly and smoothly. But my first recommendation matches Alan's: Filters can (and should) handle this task.

Alternately, you could use an actual database program, like Access or MySQL or Sqlite and then get comfortable with forms and SQL queries.

I agree a filter is the simplest (best?) solution but reckon an advanced filter might suit better than an autofilter; what is the issue with it? (you mention you looked at it with no success)

Hi all,

This query was originally recommended by me to be posted in this forum after I saw it in the General forum:

http://www.excelforum.com/excel-gene...-data-set.html

Apologies for this resulting in a duplicate post of some sorts. I assumed that the OP had given up on this thread when he told me that he wasn't in a position to use Access.

My first thought was that this sounded more like something suitable to a database than to Excel. My second (belated) thought was - like most of you - that this could simply be achieved by using autofilters.

I am still not entirely sure why the OP is not willing to go down the (far simpler) option of using this method, though he intimated that he did not wish the users to be able to view all data.

I agree with tigeravatar that a formula-based (necessarily array) solution is not going to be the best method by any means, though I have said that I will give it a go (after agreeing on a limited number of search criteria and exact matching only).

However, whilst I'm attempting that, it might be worth asking if anyone knows of a VBA solution that could allow the use of autofilters yet hide the source data from view, so allowing all options for each criteria to be seen when selecting a filter, yet only displaying the results of each selection?

Suggest to any moderators viewing that one or the other of this and the above thread are discontinued.

Regards

it is simple enough to hide the source sheet but if the data is in the workbook it will be viewable if a user really wishes to see it (they could simply filter each value in turn to build up a complete picture anyway)

with a hidden sheet code can still use filters-how complex that will be depends entirely on the sort of filtering required: combining one or more criteria on each field like an autofilter ((field1 = A or B) AND (field2 = C or D)), or combinations of criteria on different fields such as an advanced filter will allow ((field1 = A AND field2 = C) OR (field1 = B AND field2 = D)), or both

even without a real database one might still use ADO to query the source data-preferably in an external workbook

Thanks, Joseph. Yes, I did consider this issue about being able to 'build up a complete picture' if the user really wanted to. This is why I said I'm not really sure why the OP doesn't wish any of the source data to be seen - and to what 'extent' that's the case (it would be a pretty big effort to go to the extent of 'building a picture' of the entire dataset through repeated filtering), i.e. whether more for aesthetic or for data sensitivity reasons.

If you spend a minute looking at the other thread, do you think you'd be willing to attempt a solution along the lines of that you suggest? Looks like three criteria and exact matching only - is that something you could work with (perhaps with a 'very' hidden data sheet)?

Regards

perhaps tomorrow if I have some time free

Hi,

Please can you have a look at the attached?

This is not actually in the form that you requested. This is just something that I would like to start with and then for you to determine to what extent you want the search functionality to be flexible.

In fact, the attached is really an almost exact duplicate (in terms of what it produces) of the in-built filtering system using some array formulas (and hence is a bit pointless).

There are five search criteria, with the following features:

1) Any combination of all of these will produce the desired result.

2) Not all criteria must be entered - hence the default list (all blank) is one which shows, in effect, the entire dataset.

3) LastName need not be exact, but will search on the first given number of letters entered, e.g. "Al" (with all other four criteria blank) will result in records pertaining to "Arthur Albiston", "Martin Albrechtsen", "Albert Aldridge", "Norman Aldridge", "Scott Allan" and "Stanley Allan".

4) FirstName is slightly different. As well as the above, it will return a match if the string entered is found anywhere within the first names in the dataset. So, with the above-mentioned criteria still in place ("Al" for LastName), entering "rt" in FirstName will further filter this list to just "Arthur Albiston", "Martin Albrechtsen" and "Albert Aldridge".

5) Age may be filtered via the use of a lower and upper limit (inclusive).

Obviously the big issue here is that (just as with auto-filters) users will be able to access the entire dataset simply by leaving all fields blank. However, I must confess that this issue is still slightly confusing me, as, unless you wish it to be so that nothing is seen until all five criteria are filled in (and perhaps with exact matches), then I don't see you having much functionality in terms of searching.

Take Age, for example: what is the point in having this as a search criterion if you can only perform exact searches? Surely you would like to find all players between two ages? But then we would be left with the issue again that, if somebody really wanted, they could enter "0" and "100" and get access, again, to the whole dataset.

Similarly with position. If you wish you to have the ability to see, say, all Defenders who've ever played, then similarly you’d have the ability to see all Attackers…and all Goalkeepers...and all Midfielders...and suddenly you've built up a picture of the whole dataset again.

Forgive me but I really don't see the issue of data sensitivity here, which is preventing what I feel would make this a workable project; but that's your issue, not mine, and I will go ahead and make a version in which all four criteria must be entered (and will evidently have to make the Age search a single number) if that's what you really want.
However, I thought I'd first present this version to you and to get your impressions (and give you mine).

Regards

No. Not in this thread nor the other.

Sometimes - if you spend a long time over a message for example - the forum logs you out for inactivity so you unwittingly think you've sent a message when in fact you haven't (since you weren't logged on). It's quite frustrating at times - I often write or copy mine to Word if I feel it's going to be a longish post.

Regards

"I'm really pleased with your progress and am happy to take your guidance, but would it be possible to select any one of the search criteria and search on that instead of having to complete all criteria boxes?

You don't. Please re-read my previous thread, specifically point 2).

As to your other point, I think you're confusing data sensitivity and data protection. It sounds as if you're saying that it's not so much an issue that other people have access to all of the statistics that you have compiled (and even that they can, through repeated searching, end up being able to piece together their own spreadsheet consisting of the entire dataset you have compiled) but more that you don't want them to be able to tamper with the data that you make available to them. Is this correct?

This is a very different matter, and, if this is the case, there is no reason why we can't revert to the original plan of using Excel's in-built auto-filters. Although that would mean people would be able to view all the data, we can add protection to the sheet so that they are unable to in any way tamper with that data.

Regards

Hi,

Like I said, the version that I created is pretty much identical to what could be achieved using the in-built autofilters (though far less efficient).

You say you have some knowledge of these autofilters? Then you'll know how effective they can be and the degree to which they offer filtering by multiple criteria (in fact, all criteria in your sheet if you so wished, with no array-formulas to slow it down).

Perhaps you should have a look at this as an alternative. If the reason that you rejected it in the first place was through fear of data-tampering, then you shouldn't worry, as this can be easily prevented.

Let me know your thoughts on which option you'd like to go with and we can discuss data protection options.

Regards

Ok, so I'll let you decide which option you go with: you have my array-version (which I'll be happy to refine to suit your wishes) and you have auto-filters. So let's discuss workbook protection:

"...how would you suggest protecting the data in a more robust way than Excels built in worksheet and workbook protection provides?"

Wow! This really is sought-after data!! Ok, I have to confess, apart from having read the odd story about how, if someone really wants to, they can get hold of the know-how to break password-protected sheets, I've never really been in a position myself where I've had to go beyond the in-built protection options.

If you could get back to me on the first question (whether you require any amendments to the sheet I sent, if you decide to go with it) and I'll ask around to see if anyone can offer any advice on this issue of protection.

Regards

"One other question, as I add players to the dataset, will the array formula automatically recognize any additions and capture them for the search?"

Yes. All the formulas are offset to automatically take into account any new additions to the raw data.

In addition, the results sheet currently has the capacity to return up to 500 lines of data. If you foresee more than this being returned, simpy drag the formulas down as many rows as are required (though hopefully you won't need to go too far - array formulas start to slow down a sheet if they become too numerous).

Do you require any changes to the sheet I sent you whilst we wait for news on protection options?

Regards

Butting in for a moment, in terms of security, it's best you get your head around the idea now that Excel is not secure from anyone who has the least inkling to access your "locked" workbook. They'll get in.

In our company we do not email sensitive workbooks. We print a PDF of the part of the Excel wb we want them to review and they get that.

Print the file to a PDF.

When the PDF is opened, it can be searched. Just like in Excel, CTRL-F.

Sorry to butt back in here, but wasn't the idea to have some sort of searchable database?

Whilst I appreciate that you can search for text in a pdf file (CTRL+F), that's hardly the same thing, is it? And what becomes of the work I put into the Excel version if this is to be the case?

Forgive me if I'm missing something.

Regards

Ok, perhaps so. It just seemed a strange tack, knowing that CTRL+F in a pdf is hardly the greatest of search methods. But I'm glad that at least you'll be using the sheet I created.

And many thanks for your kind words in the reputation you gave me. You're very welcome and I wish you the best of luck with your project (and Excel in general!) for the future.

Regards

@XOR, I gave up long ago on the idea of securing Excel data, and anything that must remain secure does not leave our offices, that's as close as we can get.

If you have some usable security suggestions to offer seraphim, I would be of interest as well. Eveything I tried was hackable in just a few moments of effort.


@Seraphim - before printing to your PDF, go to the PAGE SETUP and set all your sheet settings... landscape, print area, etc.

Hi again,

Actually I bow down to Mr. Beaucaire on that issue, and I'm afraid I can't improve on what he's already said.

It would seem he's right that, at least with Excel files, if somebody really wants to break in, then you're probably not going to be able to stop them.

I presumed you'd agreed to go with pdfs when sharing and just keep the Excel sheet for your own personal use?

Sorry I can't be of any more help on this issue.

Regards

Originally Posted by XOR LX

....if somebody really wants to break in, then you're probably not going to be able to stop them.

Let me clarify, not "probably", but absolutely will not be able to stop them. Anyone with a web browser and 5 minutes of patience will have fully unlocked any Excel file in their possession. There are easily found web sites / techniques out there proud to be able to assist in this very thing.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.