Identifying more than one item in a list

[Lady Success]

I am creating a query worksheet where text is entered. I then want to verify
the status of that entry against a database. I need to identify anything
that contains all or part of the name listed in the query. It would look
something like this:

Sheet 1 is where the query is:

A1 "Type in drug name"
B2 Drug name is entered by user
C2 - through C6 I want the formula(s) to find the drug name shown in
B2 and if more than one instance, indicate all the instances it found.
D2 through D6 - I want the formula(s) to find the content of Column B in the
database that matches the content of C2- through C6.

The example and outcome would look like this:

The word "Nifedipine" is entered in the query sheet.

I want the query formulas to come back with the following responses:

C2 Nifedipine D2 Covered generic
C3 Nifedipine ER D3 Non-Covered Generic


Sheet 2 contains the database:

Column A Column B
NIFEDICAL XL Covered Generic
NIFEDIPINE Covered Generic
NIFEDIPINE ER Non-Covered Generic
NILSTAT Covered Generic
NIMODIPINE Covered Generic
NISOLDIPINE Covered Generic
NITREK Covered Generic
NITRO-BID Covered Generic
NITROFURANTOIN Covered Generic

Is it even possible for me to do what I want to do? I know how to use the
Vlookup function, but not sure how I can identify an entire string rather
than just the exact match and also identify more than one instance of the
name?

 

[T. Valko]

One way:

Assuming your DB is sorted or grouped so that all instances of the lookup
drug are grouped together.

Use a cell that returns the number of records found. Maybe use cell C1 and
in cell B1 you could enter: Records Found.

Enter this formula in C1:

=IF(B2="","",COUNTIF(Sheet2!A:A,"*"&B2&"*"))

Enter 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),""))

Enter this formula in D2:

=IF(C2="","",INDEX(Sheet2!B:B,MATCH(C2,Sheet2!A:A,0)))

Select both C2 and D2 and copy down to C66 or to a number of cells that is
at least equal to the max number of instances any drug that may appear in
your DB.

 

[Lady Success]

Biff,

This worked very well. Thank You! Would it be possible for you to step
through the row and index functions so I can understand better what they are
doing? I stepped it through the wizard and have a little better
understanding, but not sure that I totally understand how the functions work.
In particular, the INDEX(Sheet2!A:A) function. If just the column is
entered without indicating a row, does that allow the function to access the
entire database? I'm not sure exactly what the INDEX does. Can you explain
it a little better than the "Help"? I'm assuming that MATCH("*"&B$2&"*") is
identifying the entire string in a cell by using a wildcard?

Everytime I use a suggestion I get in this forum, I've been able to use or
expand it in future worksheets. It's been a very useful tool for me to
expand my knowledge of Excel. Thanks for all of the help you've been!

 

[T. Valko]

I'll write up an explanation tomorrow. It's 2:30 AM where I am and I'm
getting ready to call it a day!

 

[Lady Success]

No hurry. Tomorrow is just fine! It's getting very late here as well.
Thanks for just responding.

 

[Lady Success]

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. I know you said the content had 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?

 

[T. Valko]

you said the content had to be together

It doesn't have to be but when the data is sorted or grouped together it
makes it easier.

Ok, we need to tweak the formulas. This new "main" formula is more
calculation intensive and may be slower to calculate if your DB has 1000's
of rows of data.

Let's use defined named ranges in these new formulas.

Assume your DB on sheet2 is in the range A2:B500. The drug name is in column
A and the status is in column B.

Create these named ranges:

Goto the menu Insert>Name>Define
Name: Drug
Refers to: =Sheet2!$A$2:$A$500

Name: Status
Refers to: =Sheet2!$B$2:$B$500

Formula in C1 to get the count of records:

=IF(B2="","",COUNTIF(Drug,"*"&B2&"*"))

The new formula in C2 is now an array formula** :

=IF(ROWS(C$2:C2)<=C$1,INDEX(Drug,SMALL(IF(ISNUMBER(SEARCH(B$2,Drug)),ROW(Drug)),ROWS(C$2:C2))-MIN(ROW(Drug))+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Note that if you're *not* using Excel 2007 array formulas *can't* use entire
columns as range references. That's why I've defined the named ranges Drug
and Status to be specific sized ranges.

Formula in D2:

=IF(C2="","",INDEX(Status,MATCH(C2,Drug,0)))

Select both C2 and D2 and copy down as needed.

Since we changed the "main" formula I guess I no longer need to explain how
the original formula worked?

 

[Lady Success]

Thanks! This worked great! No need to explain the previous formula. I
think as I'm begin to use more of the functions in a nested fashion, I'll get
the hang of it. Thanks for the help!

 

[T. Valko]

You're welcome. Thanks for the feedback!

I've written some fairly extensive explanations of formulas and I've marked
some of them with an "identifier". If you're so inclined you can do a search
of Google Groups for the phrase: Biff exp 101.

Spend an hour a day reading posts in this newsgroup and you'll be surprised
at how much you can learn!