?
Solved

Excel vlook up type formual needed to harvest specific words

Posted on 2014-11-27
5
Medium Priority
?
89 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 33

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 33

Expert Comment

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

Accepted Solution

by:
James Elliott earned 1500 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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

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!
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

718 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