Selecting specific numbers from a cell containing multiple numbers

[JRD]

excel 2007

Look at the example below:

A B
1 Isoket; Reopro; "verapamil" "100MCG"; 11.4MLS; 0.5MLS


2 Reopro; "Isoket"; heparin "7.4ml"; 500mcg; 5000units


3 Isoket; heparin; lignocaine 350mcg; 5000 units; 10mls


Column A contains various medical drugs used in a medical procedure. Column
B contains the dose of these drugs (in the order in which drugs stated in
column A seperated by a semi-colon)

I need excel to pick out from column A the drug reopro if reopro is
contained in a cell in column A then tell me what dose was used by looking in
column B.

So in example above, in row 1 the answer should be 11.4MLS, in row 2, 7.4ml
and in row 3, N/A (i.e. no reopro was used)


Thanks for the help

John

 

[JE McGimpsey]

Can I suggest an alternative to the complicated formula that would be
necessary?

1) Insert enough columns between A & B to contain the maximum number of
drugs in A.

2) Use Data/Text to Columns/Delimited/Other:semicolon to expand each
drug entry ad each dosage into its own cell. For example:

A B C ... J K L
1 Isoket Reopro verapamil 100MCG 11.4MLS 0.5MLS


Then"

3) Determine dosage with something like this:

=IF(COUNTIF(A1:H1,"reopro")=0,"NA",INDEX(J1:Q1,
MATCH("reopro",A1:H1,FALSE)))

 

[Ron Rosenfeld]

excel 2007

Look at the example below:

A B
1 Isoket; Reopro; "verapamil" "100MCG"; 11.4MLS; 0.5MLS


2 Reopro; "Isoket"; heparin "7.4ml"; 500mcg; 5000units


3 Isoket; heparin; lignocaine 350mcg; 5000 units; 10mls


Column A contains various medical drugs used in a medical procedure. Column
B contains the dose of these drugs (in the order in which drugs stated in
column A seperated by a semi-colon)

I need excel to pick out from column A the drug reopro if reopro is
contained in a cell in column A then tell me what dose was used by looking in
column B.

So in example above, in row 1 the answer should be 11.4MLS, in row 2, 7.4ml
and in row 3, N/A (i.e. no reopro was used)


Thanks for the help

John

Easiest is to change your database as suggested by McGimpsey.

If you must keep your data the way it is, then a UDF is easiest.

To enter the UDF, <alt-F11> opens the VBEditor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

To use this, enter a formula like:

=Dose(drug_name,Drug_List,Dose_List)

drug_name may be either a string or a cell reference.

There must be at least as many dosings in the dose list as there are drugs in
the drug list.

=====================================
Option Explicit
Option Compare Text
Function Dose(Drug As String, DrugList As String, DoseList As String)
Dim Drugs, Doses
Dim i As Long
Drugs = Split(DrugList, ";")
Doses = Split(Replace(DoseList, """", ""), ";")

If UBound(Drugs) > UBound(Doses) Then
Dose = CVErr(xlErrValue)
Exit Function
End If
i = 0
Do Until Trim(Drug) = Trim(Drugs(i))
i = i + 1
If i > UBound(Drugs) Then
Dose = CVErr(xlErrNA)
Exit Function
End If
Loop
Dose = Doses(i)
End Function
==========================================
--ron

 

[Shane Devenshire]

Hi,

First, I would strongly recommend that you enter the data in a consistant
manner - sometimes you are quoting the names and sometimes not. Sometimes
you quote the values at other times you don't.

To give you an idea of the formula you would need to use here is one that
returns VALUE is it does not find the drug. Assuming the drugs are in column
A and the amounts in B and C1 contains the drug you want to check:

=IF(IF(FIND(C1,A1)<FIND(";",A1),1,IF(FIND(C1,A1)<FIND(";",A1,FIND(";",A1)+1),2,3))=1,LEFT(B1,FIND(";",B1)-1),IF(IF(FIND(C1,A1)<FIND(";",A1),1,IF(FIND(C1,A1)<FIND(";",A1,FIND(";",A1)+1),2,3))=2,MID(B1,FIND(";",B1)+1,FIND(";",B1,FIND(";",B1)+1)-FIND(";",B1)-1),MID(B1,FIND(";",B1,FIND(";",B1)+1)+1,10)))