Excel multi criteria search

[edw187]

i have been trying to create a search that allows me to search up four different criteria if necessary. the search is for an extensive address and phone book on excel that will produce an output on a new sheet with the same cell formatting as the original worksheet

i will also like to use a userform

please let me know what to do

[edw187]

Please if you have any ideas let me know

[geekgirlau]

Have you had a look at using Excel's filters to do this?

[edw187]

i have but i would rather use a userform for a easier search input

[edw187]

this is what i currently have please let me know if anyone can add to it so it does what i want

thanks

[MWE]

Unless I do not understand something here, this is not a very difficult problem. I looked at your code and it seems overly complicated. If I understand things, the problem is as follows:

1. you have a worksheet (called Data in the example) with typical phone book information (by the way, the content in the attached example is not consistent)
2. you wish to find cases that match up to four search criteria. I assume that this could be one criterion for each of four fields or four criteria for a single field or any combination.
3. you wish the user interface to be a form
4. you want the output to go to a (new) worksheet formatted as the original data sheet

You did not mention how to handle mixed case (for example if you are searching in the City field for Toronto, is TORONTO a match?)

You did not mention if you needed boolean support, for example find Antwerp in the City field but not 2000 Antwerp

If you can reply basck, I am sure I can help you.

BUT. You stated that the list was extensive. I started such a phone book list 20 years ago in Lotus123. I migrated to a flat file DB program and eventually to Paradox and finally to Access because of just the kind of problem you are facing. If you were to copy your Data sheet to Access, you could easily construct queries to search all kinds of criteria and combinations. My phone book has 3600 entries (rows in your sheet) and 70 fields. It is easily managed in Access (with minimal VBA additions to do things the way I want) and would be a cumbersome in Excel. So, have you considered a more appropriate application? Access is harder to use and harder to "work behind the scenes" than Excel, but for your application, it (or perhaps some other DB package) seems ideal.

[mdmackillop]

A little basic as I'm not quite sure what you are after.
Search will create a list based on one column + one or two criteria
Add will add a further columns + additional criteria.
Search will then return an Advanced search based on the Criteria sheet

I go along with the database suggestion, but maybe this will assist until then.

[edw187]

that is exactly what i am looking for. i have made things more complicated then necessary. hopefully you understand what i am looking for. i need to make it as user friendly because the phone book is going to be on a shared office hard drive for all employees to access. please let me know if you need anything else.

thank you for you help

[MWE]

that is exactly what i am looking for. i have made things more complicated then necessary. hopefully you understand what i am looking for. i need to make it as user friendly because the phone book is going to be on a shared office hard drive for all employees to access. please let me know if you need anything else.

thank you for you help

Attached please find an updated spreadsheet that meets most of your criteria. It actually has two search methods selected via a pull down menu:

• Find is a modified version of what you originally did. It supports up to 4 criteria and uses AutoFilter to progressively hide rows that do not meet criteria. The end results is a sheet with all non-matching rows hidden. It also allows the user to select final formatting -- either col widths as the original sheet or AutoFit to the remaining data.
• Find2 has a similar user interface and options but does explicit cell checking deleting rows that do not match criteria. The end result looks about the same. An advantage to Find2 is that it supports partial matches.

Neither approach is very elegant (just brute force code) and neither "keep" old searches. I have not included any boolean options, but that could be added (particularly to Find2) without much difficulty. I have not done much testing, but it seems to do what I think you want. Play with it a bit and let me know.