L
Lady Success
I’m trying to count the number of instances a specific entry appears and then
I want to display the instances it found in the database. My logic for the
count appears to be working ok – but it is not identifying the correct items.
In O2, my formula reads: =IF(Query!B3="","",COUNTIF(ChemName,DRUGENTRY))
In O4, my array formula reads:
{=IF(ROWS(O$4:O4)<=O$2,INDEX(DRUGLIST,SMALL(IF(ISNUMBER(SEARCH(DRUGENTRY,DRUGLIST)),ROW(ChemName)),ROWS(O$4:O4))-MIN(ROW(ChemName))+1,2),"")}
I copied the formula down to the cells below.
One tab contains the data entry of: “epoetin alfaâ€
My database looks like the following:
Drug Name Chemical Name J code
Aranesp darbepoetin alfa J0882
Aranesp darbepoetin alfa J0881
Epogen epoetin alfa J0886
Epogen epoetin alfa J0885
Epogen epoetin alfa J2505
Neumega oprelvekin J2355
Neupogen filgrastim J1440
The result I get for the count is 3 (which is right). But the items
identified as the three it found are: darbepoetin alfa (twice) and epoetin
alfa (once). I do not want to include the darbepoetin alfa (even though it
includes the search name. I just want to capture the epoetin alfa. I only
want to include exact matches to the data entry. I’ve tried modifying my
formula in several different ways, but I still can’t get it to come back with
the exact match. If you could steer me in the right direction, I would
appreciate it.
I want to display the instances it found in the database. My logic for the
count appears to be working ok – but it is not identifying the correct items.
In O2, my formula reads: =IF(Query!B3="","",COUNTIF(ChemName,DRUGENTRY))
In O4, my array formula reads:
{=IF(ROWS(O$4:O4)<=O$2,INDEX(DRUGLIST,SMALL(IF(ISNUMBER(SEARCH(DRUGENTRY,DRUGLIST)),ROW(ChemName)),ROWS(O$4:O4))-MIN(ROW(ChemName))+1,2),"")}
I copied the formula down to the cells below.
One tab contains the data entry of: “epoetin alfaâ€
My database looks like the following:
Drug Name Chemical Name J code
Aranesp darbepoetin alfa J0882
Aranesp darbepoetin alfa J0881
Epogen epoetin alfa J0886
Epogen epoetin alfa J0885
Epogen epoetin alfa J2505
Neumega oprelvekin J2355
Neupogen filgrastim J1440
The result I get for the count is 3 (which is right). But the items
identified as the three it found are: darbepoetin alfa (twice) and epoetin
alfa (once). I do not want to include the darbepoetin alfa (even though it
includes the search name. I just want to capture the epoetin alfa. I only
want to include exact matches to the data entry. I’ve tried modifying my
formula in several different ways, but I still can’t get it to come back with
the exact match. If you could steer me in the right direction, I would
appreciate it.