Im 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(ISN UMBER(SEARCH(DRUGENTRY,DRUGLIST)),ROW(ChemName)),R OWS(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 a 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. Ive tried modifying my
formula in several different ways, but I still cant get it to come back with
the exact match. If you could steer me in the right direction, I would
appreciate it.

--
Lady

I only want to include exact matches to the data entry.

Replace:
ISNUMBER(SEARCH(DRUGENTRY,DRUGLIST))

With:
DRUGLIST=DRUGENTRY

--
Biff
Microsoft Excel MVP


"Lady Success" wrote in message
...
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(ISN UMBER(SEARCH(DRUGENTRY,DRUGLIST)),ROW(ChemName)),R OWS(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 a 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.

--
Lady

That worked well for the epoetin alfa scenario, but now when I enter
filgrastim as my data entry, it gives me #N/A error.... If I'm looking for
an exact match, why doesn't it work when there's just one word and not two?
--
Lady


"T. Valko" wrote:

I only want to include exact matches to the data entry.

Replace:
ISNUMBER(SEARCH(DRUGENTRY,DRUGLIST))

With:
DRUGLIST=DRUGENTRY

--
Biff
Microsoft Excel MVP


"Lady Success" wrote in message
...
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(ISN UMBER(SEARCH(DRUGENTRY,DRUGLIST)),ROW(ChemName)),R OWS(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 a 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.

--
Lady

Could be unseen characters like spaces in either the DRUGENTRY or DRUGLIST
ranges:

filgrastim<space
<spacefilgrastim
<spacefilgrastim<space


--
Biff
Microsoft Excel MVP


"Lady Success" wrote in message
...
That worked well for the epoetin alfa scenario, but now when I enter
filgrastim as my data entry, it gives me #N/A error.... If I'm looking
for
an exact match, why doesn't it work when there's just one word and not
two?
--
Lady


"T. Valko" wrote:

I only want to include exact matches to the data entry.

Replace:
ISNUMBER(SEARCH(DRUGENTRY,DRUGLIST))

With:
DRUGLIST=DRUGENTRY

--
Biff
Microsoft Excel MVP


"Lady Success" wrote in message
...
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(ISN UMBER(SEARCH(DRUGENTRY,DRUGLIST)),ROW(ChemName)),R OWS(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 a 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.

--
Lady