Solved

Excel vlook up type formual needed to harvest specific words

Posted on 2014-11-27
5
86 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 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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Gain an elementary understanding of Blockchain technology.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
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‚Ķ

623 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