Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need to pair each item on one list to each item on another list | Excel Worksheet Functions | |||
Dropdown List - list item endings not visible if column too narrow | Excel Discussion (Misc queries) | |||
Identifying bottom of list | Excel Worksheet Functions | |||
Identifying an item in one col/row and searching an entire spreads | Excel Worksheet Functions | |||
Selecting an Item from a List and getting a different item to pop. | Excel Worksheet Functions |