Solved

Excel vlook up type formual needed to harvest specific words

Posted on 2014-11-27
5
84 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 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 NAB Show April 24-27!

Visit ATEN at NAB Show to learn how our "Seamlessly Entertaining" solutions deliver fast, precise video streaming without delays for the broadcasting and media environment. ATEN will showcase its 16x16 Modular Matrix Switch (VM1600) and KVM Over IP Solution (KE6900 series).

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
nested If/and formula needed 12 64
Longer drop-down menu for Data Validation list in Excel 8 22
Lookup - Vlookup 9 46
question on substitute function 2 26
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

756 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