Solved

Excel vlook up type formual needed to harvest specific words

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

Don't Miss ATEN at InfoComm 2017!

Visit booth #2167 to see the  new ATEN VM3200 32 x 32 Modular Matrix Switch. Other highlights include the VE8950 4K HDMI Over IP Extender, VS1912 12-Port DP Video Wall Media Player  and VK2100 ATEN Control System. Register now with Free Pass Code ATEN288!

Question has a verified solution.

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

Suggested Solutions

The advancement in technology has been a great source of betterment and empowerment for the human race, Nevertheless, this is not to say that technology doesn’t have any problems. We are bombarded with constant distractions, whether as an overload o…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

710 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