Excel Multiple Search Criteria and Display results

**Cupsay** · 02-05-2014, 09:49 PM

Hello to all,

I had a similar problem to another member of this forum - see: http://www.excelforum.com/excel-gene...ml#post3574025

In brief, a solution was sought to:

Return multiple data for a partial or even complete search criteria - e.g. the solution I was looking for concerned an excel workbook I use with all my business contacts in it (see the attached .jpg - I can't upload the actual .xls as it is too large). As is often the case, you may only remember the contact you want to find by their family name, or the product that their company sells. However, there maybe many contacts with the same family name or companies in your list that sell the same product. The solution given by Popipipo in the thread I have linked this thread to allows ALL columns to be searched (albeit one at a time). Then just show all of the matches that it found and, finally, have a reset function that returned your entire list of contacts.

So why this thread?

Well, I downloaded Popipipo's solution and modified it as you can see in my attached .jpg (with respect to renaming columns and a few cosmetic changes that suited me). Usually the very helpful and generous of their time Contributors to this forum only produce a solution that is 'proof of concept.' To make it into a 'working' workbook such things as search ranges etc., often have to be changed.

Popipipo's .xls has the VBA code, I'm sure, where it should be which, unfortunately, is a mystery to me and, even if I were to find it I'm not that confident I could correctly modify the code to expand the search range to what I want.

In a secondary problem that I caused, I tried to move the very useful count feature to the same cell as the RESET button (by centering the count script to the bottom of the cell and moving the RESET button up so both fitted) however, things started going terribly wrong after doing that - the count stopped working and when I copied it back to its original cell it stopped working as it had been... meanwhile I'd looked for the VBA to change the range by using Alt+F11 and in playing around with the codes I happened upon (and even after returning these codes to their original state) I found I'd killed the search function altogether.

Could someone explain where to find the VBA code that controls the range and, once there, how to change it. As to the secondary problem, I know some formulas need to be nested by inputting them with Ctlr+Shift+Enter... and, I assume, that a formula cannot reside in the same cell as the RESET button(?)... in moving the formula back by copying and pasting, that is where I probably went wrong when, back where it started, it wouldn't work.

Many thanks,
Cupsay.

**Cupsay** · 02-06-2014, 01:32 AM

UPDATE:

I have just been experimenting with the solution Popipipo gave and found that as long as each column had data in each row the search range extended to the last row with data... that is to say, if a row was skipped (left without data in it) then the search in that coloumn would not extend past that empty row.

I'd still like to know where the range is set but, now would like to know if the VBA can be changed to allow, say, 3 blank rows before it stops searching. This is desirable because in my first column I am putting a blank row, then a row with a letter of the alphabet (to show the start of all companies starting with that letter) and another blank row before the companies are added under that letter. This means that the rows to the right of the first column will have 3 blank rows in them at every change of the alphabet.

I also noticed that the count formula =SUBTOTAL(3,B6:B107)&" out of "&COUNTA(B6:B107)&" items found" counts 1 even if there is nothing to count - this gives an incorrect count of the number of rows of data in its resting state however, it corrects itself once you do a search - it is not important to me but, maybe it is to someone else intending to adapt this workbook to their own use.

Also, what is the significance of the 3 in this formula - I know if I change it the formula stops working.

**HaHoBe** · 02-06-2014, 02:41 AM

Hi, Cupsay,

instead of attaching a picture attach a sample workbook/your workbook. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Ciao,
Holger

**Cupsay** · 02-06-2014, 03:55 AM

Hi Holger,

On the off chance these sales people didn't want extra business I edited my thread to change the picture attached to it by erazing the company and person's names.

On closer reading of my thread you will see I explained why I can't upload my workbook - for some inexplicable reason it is over 2mB (and it is not caused by the .jpg files I have attached as comments to the Name column - I removed them and this workbook was still too big for your forum restriction on upload size). Nothwithstanding I did try to upload my workbook, the link to the thread that I had placed at the front of this thread explained where the workbook I am using could be viewed/downloaded for those that might be kind enough to answer the actual questions I asked (bearing in mind the later update).

Ciao,
Cupsay