Solved

Excel vlook up type formual needed to harvest specific words

Posted on 2014-11-27
5
82 Views
Last Modified: 2014-12-02
I have a dataset with a very long drug names (drug compound mixture) in one field.  Here is sort of an example:
10% Neostigmine OR anesthesia 1mg 5ml BD syringe 1540 Methyl 18%

The main drug in this compound is Neostigmine.
I have a list of about 100 drugs and this is one.  I need to create some sort of look up that will look for and bring in the single main drug name of (Neostigmine) beside the very long compound name.

The main drug names within the compound name can be located in various places within the long compound name.

I can either do this in EXCEL or ACCESS - help appreciated.

Thanks
0
Comment
Question by:vpopper
  • 2
  • 2
5 Comments
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40469031
How are you expecting Excel to recognise the drug name within the compound? Do you have a list of drug names that it can compare with?

Thanks
Rob H
0
 

Author Comment

by:vpopper
ID: 40469054
Yes - I have two tabs.  One tab contains spend data (about 800 rows) with one field (column) that contains a LONG drug compound names. The second tab contains a list of about 100 drug names ie. below.
I need to look for these specific drug names within the data (long drug compound name) so I can group spend to specific drugs.  Thanks


Drug Name
Atropine
Bupivacaine
Ephedrine
Epinephrine
Esmolol
Fentanyl
Glycopyrrolate
Hydromorphone
Ketamine
Labetalol
Lidocaine
Methohexital
Midazolam
Morphine
Neostigmine
Phenylephrine
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40469073
Can you upload a sample file?
0
 
LVL 12

Accepted Solution

by:
James Elliott earned 500 total points
ID: 40469121
You could create your own function:

In your workbook hit Alt+F11
Click Insert=>New Module
Paste the following code

Public Function EE(pTarget As String, rng As Range) As String

Dim arr As Variant
Dim cnt As Long
Dim x As Long

arr = Split(pTarget, " ")

For x = LBound(arr) To UBound(arr)
    If Not rng.Find(arr(x)) Is Nothing Then
        EE = arr(x)
        Exit Function
    End If
Next x

End Function

Open in new window


You can now use in your spreadsheet like this:

=EE(A1, Sheet2!$A$1:$A$20)

Open in new window



Where A1 is the cell containing the long drug compound, and Sheet2!$A$1:$A$20 is the location of the single-word list.

Rgds
0
 

Author Closing Comment

by:vpopper
ID: 40477083
Works pretty good but having a few issues...but can make it work.
Thanks
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question