Drug database

ArsenalFan1

New Member
Joined
Sep 18, 2006
Messages
8
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.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You can use:

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

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

yee388

Well-known Member
Joined
Mar 7, 2004
Messages
1,374
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,960
Messages
5,525,914
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top