Search for content in database

[Lady Success]

I am doing a query in Sheet1 against a database in Sheet2. In Sheet1, I entered the following information:

B2 is used for the entry to be searched on
I used C1 to return the number of records found.

To find a count of all instances of B2, I entered this formula in C1:
=IF(B2="","",COUNTIF(Sheet2!A:A,"*"&B2&"*"))

I entered this formula in C2:
=IF(B$2="","",IF(ROWS(C$2:C2)<=C$1,INDEX(Sheet2!A:A,MATCH("*"&B$2&"*",Sheet2!A:A,0)+ROWS(C$2:C2)-1),""))


I initially used the formula on a subset of test data and it worked great. However, when I applied it to the entire database, I found that there were entries in the database that did not fall sequentially because of the wording.

The database looks like this:
Drug Name Coverage Status
ACETIC ACID/HYDROCORTISONE Covered Generic
ACETYLCYSTEINE Covered Generic
ACIDIC VAGINAL Covered Generic
ACNE MEDICATION Covered Generic
ACTICIN Covered Generic
ACYCLOVIR Covered Generic
ADRENALIN CHLORIDE Covered Generic
ADVANCED NATALCARE Covered Generic
HYDROCORTISONE Covered Generic
HYDROCORTISONE ACETATE Covered Generic
HYDROCORTISONE BUTYRATE Covered Generic
HYDROCORTISONE VALERATE Covered Generic
HYDROGESIC Covered Generic
HYDROMORPHONE HCL Covered Generic

Searching for Hydrocotisone, it found 5 entries (which is correct).
However, because ACETIC ACID/HYDROCORTISONE is found first in the list, it pulls up the five drugs following that and as a result does not identify all the others correctly. The formula in C2 requires the content to be together (and in most cases it is), but there may be cases such as this where it is not. Do you have any ideas on how I can identify the correct five?


​

 

[Fazza]

Hi,

Maybe a parameterised query would be better?

Here is an example http://www.dailydoseofexcel.com/archives/2004/12/13/

And this one might help too http://www.dailydoseofexcel.com/archives/2004/12/13/

Suggest you google for similar if required. (I'm short of time right now.)

HTH, Fazza

 

[tusharm]

In addition to Nick Hodge's post that Fazza shared, you may want to see
Building and using a relational database in Excel (with a little help from MS Query)
http://www.tushar-mehta.com/excel/newsgroups/rdbms_in_excel/index.html