Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching data
I have a sheet that contains a sheet where the user enters
data(Sheet1=Query), another where it gathers information (Sheet2=Qualifications), and a sheet that contains a database (Sheet3=Database). ColA: Drug Name ColB: Chemical Name ColC: HCPCS The DRUGENTRY cell in the Query sheet can pull the name of the drug from the Drug Name column or the Chemical name. In Sheet 2, I have cell D36 counting the number of instances the name of the drug is entered into Sheet1. The formula used in that cell is: =IF(DRUGENTRY="","",COUNTIF(DrgList,"*"&DRUGENTRY& "*")) In Cell D37 €“ D51, I used the following array formula to identify the name of the drugs identified in the number counted in D36. {=IF(D$36="","",IF(ROWS(D$37:D37)<=D$36,INDEX(DrgL ist,SMALL(IF(ISNUMBER(SEARCH(DRUGENTRY,DrgList)),R OW(DrgList)),ROWS(D$37:D37))-MIN(ROW(DrgList))+1,1),""))} In Cell E37, I used the following array formula to identify the HCPCS code associated with the drug. {=IF(D$36="","",IF(ROWS(E$37:E37)<=D$36,INDEX(DrgL ist,SMALL(IF(ISNUMBER(SEARCH(D37,DrgList)),ROW(Drg List)),ROWS(E$37:E37))-MIN(ROW(DrgList))+1,3),""))} My problem is, if D36 counts the same drug (i.e. Gamastan, - listed in the DrugName Col) multiple times, D37-D50 lists Gamastan multiple times €“ which is fine. E36-50 is then able to identify the corresponding HCPCS codes. However, if I change the DRUGENTRY to €śribavirin€ť which is listed in the €śChemical Name€ť column in the database, D37;D39 shows the drug names of the drugs associated with the chemical name, ribavirin (i.e. Copegus, Rebetol, Ribapak) €“ which is what I want. But E36 indicates the HCPCS associated with Copegus, but then indicates #NUM! error on the remaining number of drugs. What am I doing wrong in my formula? -- Lady |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching data
Can you provide a small sample of the data, along with the actual
expected results? -- Domenic Microsoft Excel MVP www.xl-central.com Your Quick Reference to Excel Solutions In article , Lady Success wrote: I have a sheet that contains a sheet where the user enters data(Sheet1=Query), another where it gathers information (Sheet2=Qualifications), and a sheet that contains a database (Sheet3=Database). ColA: Drug Name ColB: Chemical Name ColC: HCPCS The DRUGENTRY cell in the Query sheet can pull the name of the drug from the Drug Name column or the Chemical name. In Sheet 2, I have cell D36 counting the number of instances the name of the drug is entered into Sheet1. The formula used in that cell is: =IF(DRUGENTRY="","",COUNTIF(DrgList,"*"&DRUGENTRY& "*")) In Cell D37 €“ D51, I used the following array formula to identify the name of the drugs identified in the number counted in D36. {=IF(D$36="","",IF(ROWS(D$37:D37)<=D$36,INDEX(DrgL ist,SMALL(IF(ISNUMBER(SEARCH (DRUGENTRY,DrgList)),ROW(DrgList)),ROWS(D$37:D37))-MIN(ROW(DrgList))+1,1),"")) } In Cell E37, I used the following array formula to identify the HCPCS code associated with the drug. {=IF(D$36="","",IF(ROWS(E$37:E37)<=D$36,INDEX(DrgL ist,SMALL(IF(ISNUMBER(SEARCH (D37,DrgList)),ROW(DrgList)),ROWS(E$37:E37))-MIN(ROW(DrgList))+1,3),""))} My problem is, if D36 counts the same drug (i.e. Gamastan, - listed in the DrugName Col) multiple times, D37-D50 lists Gamastan multiple times €“ which is fine. E36-50 is then able to identify the corresponding HCPCS codes. However, if I change the DRUGENTRY to €śribavirin€ť which is listed in the €śChemical Name€ť column in the database, D37;D39 shows the drug names of the drugs associated with the chemical name, ribavirin (i.e. Copegus, Rebetol, Ribapak) €“ which is what I want. But E36 indicates the HCPCS associated with Copegus, but then indicates #NUM! error on the remaining number of drugs. What am I doing wrong in my formula? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching data
The database contents would look something like this:
ColA ColB ColC Drug Name Chemical Name HCPCS Copegus ribavirin J8499 Rebetol ribavirin No JCode Ribapak ribivarin J0880 Ribaphere ribivarin j0880 Gamastan gamma glob J1460 Gamastan gamma glob J1470 Gamastan gamma glob J1500 If Query sheet indicates Chemical Name: "Ribavirin", I want two things to happen in my "qualifications" sheet. I want to list the name of the drug associated with ribavirin and their corresponding HCPCS code (i.e. Copegus (Col A) and J8499 (Col B); Rebetol (Col A) and No Jcode (Col B), etc. If the Query sheet indicates Brand Name: "Gamastan" I want the qualifications sheet to indicate Gamastan (Col A1) and J1460 (Col B1), Gamastan (Col A2) and J1470 (Col B2), etc The Gamastan scenario works ok with the current formula, but the Ribavirin does not. I'm sure there's a glitch in my formula, but I just haven't been able to identify it. Thanks for any help! (I have a dickens of a problem accessing this post and replying to it. I ended up creating a new login. Is there a problem?) "Domenic" wrote: Can you provide a small sample of the data, along with the actual expected results? -- Domenic Microsoft Excel MVP www.xl-central.com Your Quick Reference to Excel Solutions In article , Lady Success wrote: I have a sheet that contains a sheet where the user enters data(Sheet1=Query), another where it gathers information (Sheet2=Qualifications), and a sheet that contains a database (Sheet3=Database). ColA: Drug Name ColB: Chemical Name ColC: HCPCS The DRUGENTRY cell in the Query sheet can pull the name of the drug from the Drug Name column or the Chemical name. In Sheet 2, I have cell D36 counting the number of instances the name of the drug is entered into Sheet1. The formula used in that cell is: =IF(DRUGENTRY="","",COUNTIF(DrgList,"*"&DRUGENTRY& "*")) In Cell D37 €€ś D51, I used the following array formula to identify the name of the drugs identified in the number counted in D36. {=IF(D$36="","",IF(ROWS(D$37:D37)<=D$36,INDEX(DrgL ist,SMALL(IF(ISNUMBER(SEARCH (DRUGENTRY,DrgList)),ROW(DrgList)),ROWS(D$37:D37))-MIN(ROW(DrgList))+1,1),"")) } In Cell E37, I used the following array formula to identify the HCPCS code associated with the drug. {=IF(D$36="","",IF(ROWS(E$37:E37)<=D$36,INDEX(DrgL ist,SMALL(IF(ISNUMBER(SEARCH (D37,DrgList)),ROW(DrgList)),ROWS(E$37:E37))-MIN(ROW(DrgList))+1,3),""))} My problem is, if D36 counts the same drug (i.e. Gamastan, - listed in the DrugName Col) multiple times, D37-D50 lists Gamastan multiple times €€ś which is fine. E36-50 is then able to identify the corresponding HCPCS codes. However, if I change the DRUGENTRY to €œribavirin€ which is listed in the €œChemical Name€ column in the database, D37;D39 shows the drug names of the drugs associated with the chemical name, ribavirin (i.e. Copegus, Rebetol, Ribapak) €€ś which is what I want. But E36 indicates the HCPCS associated with Copegus, but then indicates #NUM! error on the remaining number of drugs. What am I doing wrong in my formula? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching data
For simplicity, let's assume that A2:C8 contains the data, and E2
contains the drug or chemical name of interest. Then try... F2: =IF(ISNUMBER(MATCH("*"&E2&"*",B2:B8,0)),COUNTIF(B2 :B8,"*"&E2&"* "),COUNTIF(A2:A8,"*"&E2&"*")) G2, confirmed with CONTROL+SHIFT+ENTER, and copied down: =IF(ROWS(G$2:G2)<= $F$2,INDEX($A$2:$A$8,SMALL(IF(ISNUMBER(SEARCH($E$2 ,$A$2:$A$8)),ROW($A$2:$ A$8)-ROW($A$2)+1,IF(ISNUMBER(SEARCH($E$2,$B$2:$B$8)),RO W($A$2:$A$8)- ROW($A$2)+1)),ROWS(G$2:G2))),"") H2, confirmed with CONTROL+SHIFT+ENTER, and copied down: =IF(ROWS(H$2:H2)<= $F$2,INDEX($C$2:$C$8,SMALL(IF(ISNUMBER(SEARCH($E$2 ,$A$2:$A$8)),ROW($A$2:$ A$8)-ROW($A$2)+1,IF(ISNUMBER(SEARCH($E$2,$B$2:$B$8)),RO W($A$2:$A$8)- ROW($A$2)+1)),ROWS(H$2:H2))),"") -- Domenic Microsoft Excel MVP www.xl-central.com Your Quick Reference to Excel Solutions In article , Lady wrote: The database contents would look something like this: ColA ColB ColC Drug Name Chemical Name HCPCS Copegus ribavirin J8499 Rebetol ribavirin No JCode Ribapak ribivarin J0880 Ribaphere ribivarin j0880 Gamastan gamma glob J1460 Gamastan gamma glob J1470 Gamastan gamma glob J1500 If Query sheet indicates Chemical Name: "Ribavirin", I want two things to happen in my "qualifications" sheet. I want to list the name of the drug associated with ribavirin and their corresponding HCPCS code (i.e. Copegus (Col A) and J8499 (Col B); Rebetol (Col A) and No Jcode (Col B), etc. If the Query sheet indicates Brand Name: "Gamastan" I want the qualifications sheet to indicate Gamastan (Col A1) and J1460 (Col B1), Gamastan (Col A2) and J1470 (Col B2), etc The Gamastan scenario works ok with the current formula, but the Ribavirin does not. I'm sure there's a glitch in my formula, but I just haven't been able to identify it. Thanks for any help! (I have a dickens of a problem accessing this post and replying to it. I ended up creating a new login. Is there a problem?) "Domenic" wrote: Can you provide a small sample of the data, along with the actual expected results? -- Domenic Microsoft Excel MVP www.xl-central.com Your Quick Reference to Excel Solutions In article , Lady Success wrote: I have a sheet that contains a sheet where the user enters data(Sheet1=Query), another where it gathers information (Sheet2=Qualifications), and a sheet that contains a database (Sheet3=Database). ColA: Drug Name ColB: Chemical Name ColC: HCPCS The DRUGENTRY cell in the Query sheet can pull the name of the drug from the Drug Name column or the Chemical name. In Sheet 2, I have cell D36 counting the number of instances the name of the drug is entered into Sheet1. The formula used in that cell is: =IF(DRUGENTRY="","",COUNTIF(DrgList,"*"&DRUGENTRY& "*")) In Cell D37 ‰覗 D51, I used the following array formula to identify the name of the drugs identified in the number counted in D36. {=IF(D$36="","",IF(ROWS(D$37:D37)<=D$36,INDEX(DrgL ist,SMALL(IF(ISNUMBER(SE ARCH (DRUGENTRY,DrgList)),ROW(DrgList)),ROWS(D$37:D37))-MIN(ROW(DrgList))+1,1), "")) } In Cell E37, I used the following array formula to identify the HCPCS code associated with the drug. {=IF(D$36="","",IF(ROWS(E$37:E37)<=D$36,INDEX(DrgL ist,SMALL(IF(ISNUMBER(SE ARCH (D37,DrgList)),ROW(DrgList)),ROWS(E$37:E37))-MIN(ROW(DrgList))+1,3),""))} My problem is, if D36 counts the same drug (i.e. Gamastan, - listed in the DrugName Col) multiple times, D37-D50 lists Gamastan multiple times ‰覗 which is fine. E36-50 is then able to identify the corresponding HCPCS codes. However, if I change the DRUGENTRY to ‰裉ribavirin‰� which is listed in the ‰裉Chemical Name‰� column in the database, D37;D39 shows the drug names of the drugs associated with the chemical name, ribavirin (i.e. Copegus, Rebetol, Ribapak) ‰覗 which is what I want. But E36 indicates the HCPCS associated with Copegus, but then indicates #NUM! error on the remaining number of drugs. What am I doing wrong in my formula? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
matching Data | Excel Worksheet Functions | |||
Matching a column of new data to existing larger data set. | Excel Worksheet Functions | |||
Matching identical data using data only once in the matching proce | Excel Discussion (Misc queries) | |||
Can I merge data in 2 sheets matching rows of data by last name? | Excel Discussion (Misc queries) | |||
Matching data and linking it to the matching cell | Links and Linking in Excel |