Drug database

[ArsenalFan1]

Hi,

I have data in the following format

Drug Name---------Source-----Amount---Count
Actonel 30Mg-------Single------10---------2
Actonel 20Mg-------Brand------20---------3
Paxil 230------------Single------30---------3
Paxil CF 40--------- Single------10---------1
Rapamune Oral----Generic-----20---------2
Rapumune Tab-----Generic----20----------1
Singulair 10Mg-----Single-------5-----------4
Zoloft 20------------Single-------10---------6
Zoloft 40------------Single-------40---------8

Now this is a short sample. There may be 150 drugs. I dont know the names. I am trying to do the following: Add the amount for the same drug and the count and get the percentage of the total amount. For e.g.

Drug Name---------Source-----Amount---Count---Drg Amt--Drg Cnt--%
Actonel 30Mg-------Single------10---------2--------------------------------
Actonel 20Mg-------Brand------20---------3--------30-----------5-----30/165
Paxil 230------------Single------30---------3--------------------------------
Paxil CF 40--------- Single------10---------1-------40----------4------40/165
Rapamune Oral----Generic-----20---------2------------------------------
Rapumune Tab-----Generic----20----------1------40----------3------40/165
Singulair 10Mg-----Single-------5-----------4------5-----------4------5/165
Zoloft 20------------Single-------10---------6--------------------------------
Zoloft 40------------Single-------40---------8------50----------14-----50/165
TOTAL----------------------------165--------------------------------------------

All the drugs are in alphatic order. I have tried the following but its so time consuming and i have to do this for atleast 100 different sheets. I think coming up with a solution might save more time and my fingers.

I am doing the following but its not fullproof. Adding another column and doing LEFT(DRUG NAME,7) but i run into problems if two drugs have similar first 7 chars. If i used 10 then same drugs become different e.g. Paxil 3 and Paxil 4. After that i did SumIF with shorter drug name and manually removed the dulicates.

Is there an easier way? For e.g. make the drug names shorter by using the first white space it finds? Since its in alphabetic order, add same drug names until it comes to a different name?

Any help or ideas are appreciated.

 

[Andrew Poulsom]

You can use:

=LEFT(DRUG NAME,FIND(" ",DRUG NAME)-1)

but you will get an error if there is no space.

 

[yee388]

I would use Andrew's formula to get a "Category" column with just the first name of each drug, then crate a Pivot Table to get your counts, sums, and % of total.