Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 28
Default Identifying more than one item in a list

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?


--
Lady
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Identifying more than one item in a list

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 C6:D6 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.

--
Biff
Microsoft Excel MVP


"Lady Success" wrote in message
...
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?


--
Lady



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 28
Default Identifying more than one item in a list

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!


--
Lady


"T. Valko" wrote:

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 C6:D6 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.

--
Biff
Microsoft Excel MVP


"Lady Success" wrote in message
...
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?


--
Lady




  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Identifying more than one item in a list

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!

--
Biff
Microsoft Excel MVP


"Lady Success" wrote in message
...
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!


--
Lady


"T. Valko" wrote:

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 C6:D6 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.

--
Biff
Microsoft Excel MVP


"Lady Success" wrote in message
...
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?


--
Lady






  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 28
Default Identifying more than one item in a list

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


"Lady Success" wrote:

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!


--
Lady


"T. Valko" wrote:

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 C6:D6 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.

--
Biff
Microsoft Excel MVP


"Lady Success" wrote in message
...
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?


--
Lady






  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 28
Default Identifying more than one item in a list

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?

--
Lady


"T. Valko" wrote:

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 C6:D6 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.

--
Biff
Microsoft Excel MVP


"Lady Success" wrote in message
...
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?


--
Lady




  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Identifying more than one item in a list

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 InsertNameDefine
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?


--
Biff
Microsoft Excel MVP


"Lady Success" wrote in message
...
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?

--
Lady


"T. Valko" wrote:

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 C6:D6 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.

--
Biff
Microsoft Excel MVP


"Lady Success" wrote in message
...
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?


--
Lady






  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 28
Default Identifying more than one item in a list

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!
--
Lady


"T. Valko" wrote:

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 InsertNameDefine
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?


--
Biff
Microsoft Excel MVP


"Lady Success" wrote in message
...
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?

--
Lady


"T. Valko" wrote:

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 C6:D6 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.

--
Biff
Microsoft Excel MVP


"Lady Success" wrote in message
...
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?


--
Lady






  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Identifying more than one item in a list

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!

--
Biff
Microsoft Excel MVP


"Lady Success" wrote in message
...
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!
--
Lady


"T. Valko" wrote:

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 InsertNameDefine
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?


--
Biff
Microsoft Excel MVP


"Lady Success" wrote in message
...
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?

--
Lady


"T. Valko" wrote:

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 C6:D6 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.

--
Biff
Microsoft Excel MVP


"Lady Success" wrote in
message
...
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?


--
Lady








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
I need to pair each item on one list to each item on another list Peter R. Excel Worksheet Functions 1 August 24th 07 03:04 AM
Dropdown List - list item endings not visible if column too narrow AK9955 Excel Discussion (Misc queries) 2 April 27th 07 09:02 AM
Identifying bottom of list T De Villiers Excel Worksheet Functions 1 August 23rd 05 03:48 PM
Identifying an item in one col/row and searching an entire spreads Bennie Excel Worksheet Functions 0 August 19th 05 01:49 AM
Selecting an Item from a List and getting a different item to pop. Matt Excel Worksheet Functions 1 December 7th 04 02:37 PM


All times are GMT +1. The time now is 07:30 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"