View Full Version : Partial String Search cross sheet reference
ajilejay
10-09-2016, 11:57 AM
What I would like to do is search sheet2 for a match of the drug name in sheet 1 and if there is a match then I want column B in sheet1 to read High-Alert. I have only been able to do this backwards to search sheet 1 for a match from sheet 2. The issue being that sheet 1 has a large amount of extra information and sheet 2 just has the drug name I only want to search the drug name which is general the first word in the cell.
Sheet 1 column A
If match found I want this column to read high alert
MAGNESIUM OXIDE 400 (241.3 MG) MG TABS
MECLIZINE HCL 25 MG TABS
MELATONIN 3 MG TABS
metFORMIN HCL 1000 MG TABS
High Alert
methadone HCL 10 MG TABS
METOCLOPRAMIDE HCL 10 MG TABS
METOCLOPRAMIDE HCL 5 MG TABS
metoprolol succinate ER 100 MG TB24
metoprolol succinate ER 25 MG TB24
metoprolol succinate ER 50 MG TB24
metoprolol tartrate 100 MG TABS
metoprolol tartrate 25 MG TABS
metoprolol tartrate 50 MG TABS
metroNIDAZOLE 500 MG TABS
MIRTAZAPINE 15 MG TABS
MONTELUKAST SODIUM 10 MG TABS
morphine SULFATE 15 MG TABS
morphine SULFATE ER 15 MG TBCR
morphine SULFATE ER 30 MG TBCR
MULTIVITAMIN TABS
MULTIVITAMIN WITH MINERALS TABS
NITROFURANTOIN MONOHYD MACRO 100 MG CAPS
OLANZapine 5 MG TABS
OLANZapine 5 MG TBDP
omeprazole 20 MG CPDR
ONDANSETRON 4 MG ORAL DISINTEGRATING
OSELTAMIVIR PHOSPHATE 75 MG CAPS
OXYBUTYNIN CHLORIDE 5 MG TABS
OYSTER CALCIUM 1250 MG TABS
PANTOPRAZOLE SODIUM 40 MG TBEC
PARoxetine HCL 20 MG TABS
PHENAZOPYRIDINE HCL 200 MG TABS
PHENYTOIN SODIUM EXTENDED 100 MG CAPS
POTASSIUM & SODIUM PHOSPHATES 280-160-250 MG PACK
POTASSIUM CHLORIDE CRYS ER 10 MEQ TBCR
POTASSIUM CHLORIDE CRYS ER 20 MEQ TBCR
PRAVASTATIN SODIUM 20 MG TABS
predniSONE 10 MG TABS
High Alert
predniSONE 20 MG TABS
High Alert
Sheet 2
heparin
Imuran
lingaliptin
metformin
metformin
metformin ER
metformin XR
methotrexate
miglitol
muromonab
Neoral
pioglitazone
prednisone
propylthiouracil
ajilejay
10-09-2016, 02:25 PM
Anybody out there?
This is what I have but the problem is it searches the entire cell and all I want is the first word.
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(A2,Sheet2!F:F)))>0,"YES","NO")
JKwan
10-09-2016, 02:47 PM
give this a try
Sub FindDrugs()
Dim Found As Range
Dim WSInput As Worksheet
Dim WSOutput As Worksheet
Dim lRow As Long
Dim LastRow As Long
Dim FoundDrug As Range
Set WSInput = ThisWorkbook.Worksheets("Sheet2")
Set WSOutput = ThisWorkbook.Worksheets("Sheet1")
LastRow = FindLastRow(WSInput, "A")
With WSOutput
.Select
.Columns("B:B").ClearContents
For lRow = 1 To LastRow
Set Found = Find_All(WSInput.Cells(lRow, "A"), _
.Range("A1:A39"))
If Not (Found Is Nothing) Then
Select Case Found.Count
Case Is = 1
.Cells(Found.Row, "B") = "High Alert"
Case Else
For Each FoundDrug In Found
.Cells(FoundDrug.Row, "B") = "High Alert"
Next FoundDrug
End Select
End If
Next lRow
End With
Set WSInput = Nothing
Set WSOutput = Nothing
End Sub
Function FindLastRow(ByVal WS As Worksheet, ColumnLetter As String) As Long
' This function will fine the last row based on the Column that is sent to it.
FindLastRow = WS.Range(ColumnLetter & Rows.Count).End(xlUp).Row
End Function
Function Find_All(Find_Item As Variant, Search_Range As Range, _
Optional LookIn As XlFindLookIn = xlValues, _
Optional LookAt As XlLookAt = xlPart, _
Optional MatchCase As Boolean = False) As Range
Dim c As Range, FirstAddress As String
Set Find_All = Nothing
With Search_Range
Set c = .Find( _
What:=Find_Item, _
LookIn:=LookIn, _
LookAt:=LookAt, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=MatchCase, _
SearchFormat:=False) 'Delete this term for XL2000 and earlier
If Not c Is Nothing Then
Set Find_All = c
FirstAddress = c.Address
Do
Set Find_All = Union(Find_All, c)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
End Function
ajilejay
10-09-2016, 03:17 PM
ok this is going to take some time for me to test here is the actual list i'm working with to make things a bit easier. The list goes all the way to 600 and I am somewhat hoping to do this with a formula but I am grateful for whatever help I can get.
This is the Master List
Location
Note
Vlookup
ERX
Medication
Orderint
PAR
Usage/Year
PS Usage/Year
Is High-Alert?
Is P-Code?
Is HD?
PO
1
101
ACETAMINOPHEN 325 MG TABS
100
Bitmap Bitmap
*
3423
PO
2
102
ACETAMINOPHEN 500 MG TABS
20
Bitmap Bitmap
*
637
PO
3
8972
ACYCLOVIR 800 MG TABS
10
Bitmap
*
44
PO
4
310
ALLOPURINOL 100 MG TABS
5
Bitmap Bitmap
*
225
2
PO
5
325
ALPRAZolam 0.5 MG TABS
5
Bitmap Bitmap
*
215.5
PO
6
9066
AMIODARONE HCL 200 MG TABS
5
Bitmap Bitmap
*
142
PO
7
9069
amLODIPine BESYLATE 10 MG TABS
10
Bitmap Bitmap
*
330.5
PO
8
9071
amLODIPine BESYLATE 5 MG TABS
10
Bitmap Bitmap
*
336
PO
9
450
AMOXICILLIN 250 MG CAPS
5
Bitmap Bitmap
*
190
PO
10
33228
AMOXICILLIN-POT CLAVULANATE 875-125 MG TABS
10
Bitmap Bitmap
*
260.5
PO
11
681
ASPIRIN 325 MG TABS
5
Bitmap Bitmap
*
144
PO
12
680
ASPIRIN 81 MG CHEW
10
Bitmap Bitmap
*
398.5
PO
13
13654
ASPIRIN EC 325 MG TBEC
20
Bitmap Bitmap
*
1207
PO
14
14113
ASPIRIN EC 81 MG TBEC
30
Bitmap Bitmap
*
1260
PO
15
27644
ASPIRIN-DIPYRIDAMOLE ER 25-200 MG CP12
5
Bitmap Bitmap
*
103
PO
16
717
ATENOLOL 25 MG TABS
5
Bitmap Bitmap
*
108
PO
17
718
ATENOLOL 50 MG TABS
5
Bitmap Bitmap
*
104.5
PO
18
19176
atorvastatin CALCIUM 10 MG TABS
5
Bitmap Bitmap
*
169.5
PO
19
19178
atorvastatin CALCIUM 20 MG TABS
10
Bitmap Bitmap
*
351.5
PO
20
19177
atorvastatin CALCIUM 40 MG TABS
20
Bitmap Bitmap
*
1015.5
PO
21
20943
AZITHROMYCIN 250 MG TABS
20
Bitmap Bitmap
*
824
PO
22
860
BACLOFEN 10 MG TABS
10
Bitmap Bitmap
*
324
PO
23
988
BENZONATATE 100 MG CAPS
5
Bitmap Bitmap
*
142
PO
24
9310
BUMETANIDE 1 MG TABS
5
Bitmap Bitmap
*
108
PO
25
18386
buPROPion HCL ER (SR) 150 MG TB12
5
Bitmap Bitmap
*
155
PO
26
9323
busPIRone HCL 10 MG TABS
5
Bitmap Bitmap
*
200.5
PO
27
9324
busPIRone HCL 5 MG TABS
5
Bitmap Bitmap
*
191
PO
TI
28
9385
CALCIUM CARBONATE antacid 500 MG CHEW
10
Bitmap Bitmap
*
451.5
PO
TI
29
76978
CALCIUM CARBONATE-VITAMIN D 600-400 MG-UNIT TABS
5
Bitmap Bitmap
*
168
PO
30
11046
CALCIUMLYCARBOPHIL 625 MG TABS
5
Bitmap Bitmap
*
184.5
PO
31
1357
carBAMazepine 200 MG TABS
5
Bitmap Bitmap
*
124.5
PO
32
9407
CARBIDOPA-LEVODOPA 25-100 MG TABS
10
Bitmap Bitmap
*
301
7
PO
33
15749
carvedilol 12.5 MG TABS
5
Bitmap Bitmap
*
246.5
PO
34
15748
carvedilol 25 MG TABS
5
Bitmap Bitmap
*
202
PO
35
18551
carvedilol 3.125 MG TABS
5
Bitmap Bitmap
*
259.5
PO
36
15747
carvedilol 6.25 MG TABS
5
Bitmap Bitmap
*
230.5
PO
37
22289
CEFDINIR 300 MG CAPS
5
Bitmap
*
92
PO
38
24500
CELECOXIB 100 MG CAPS
5
Bitmap
*
19.5
PO
39
9500
CEPHALEXIN 500 MG CAPS
10
Bitmap Bitmap
*
407
ajilejay
10-09-2016, 03:19 PM
Sorry I was having difficulty with that post here is the sample sheet that it checks against to see if a medication is high alert
High Alert
acarbose
alogliptin
apixaban
azathioprine
basiliximab
bromocriptine
canagliflozin
carbamazepine
colesevelam
cyclosporine
daclizumab
dalteparin
edoxaban tosylate
eliquis
enoxaparin
fondaparinux
glimepiride
glipizide
glyburide
heparin
Imuran
lingaliptin
metformin
metformin
metformin ER
metformin XR
methotrexate
miglitol
muromonab
Neoral
pioglitazone
prednisone
propylthiouracil
repaglinide
rivaroxaban
rosiglitazone
Sandimmune
SangCya
saxagliptin
simulect
sitagliptin
tinzaparin
warfarin
xarelto
zenapax
JKwan
10-09-2016, 03:30 PM
why don't you post the workbook?
ajilejay
10-09-2016, 03:48 PM
Sorry I didn't know that was a option
ajilejay
10-09-2016, 03:51 PM
What I am trying to do is get Sheet 2 (Drug List) column L to search it's corresponding drug name in sheet 6 (High Alert) for a match. If there is a match I want L to read High alert.
For instance L2 Should be blank because it is not listed in colum A of sheet 6
JKwan
10-09-2016, 04:28 PM
try this
Sub FindDrugs()
Dim Found As Range
Dim WSInput As Worksheet
Dim WSOutput As Worksheet
Dim lRow As Long
Dim LastRow As Long
Dim FoundDrug As Range
Dim OutLastRow As Long
Set WSInput = ThisWorkbook.Worksheets("High Alert")
Set WSOutput = ThisWorkbook.Worksheets("Drug List")
LastRow = FindLastRow(WSInput, "A")
OutLastRow = FindLastRow(WSOutput, "L")
If OutLastRow = 1 Then OutLastRow = 2
With WSOutput
.Select
.Range("L2:L" & OutLastRow).Clear
OutLastRow = FindLastRow(WSOutput, "F")
For lRow = 2 To LastRow
Set Found = Find_All(WSInput.Cells(lRow, "A"), _
.Range("F1:F" & OutLastRow))
If Not (Found Is Nothing) Then
Select Case Found.Count
Case Is = 1
.Cells(Found.Row, "L") = "High Alert"
Case Else
For Each FoundDrug In Found
.Cells(FoundDrug.Row, "L") = "High Alert"
Next FoundDrug
End Select
End If
Next lRow
End With
Set WSInput = Nothing
Set WSOutput = Nothing
End Sub
Function FindLastRow(ByVal WS As Worksheet, ColumnLetter As String) As Long
' This function will fine the last row based on the Column that is sent to it.
FindLastRow = WS.Range(ColumnLetter & Rows.Count).End(xlUp).Row
End Function
Function Find_All(Find_Item As Variant, Search_Range As Range, _
Optional LookIn As XlFindLookIn = xlValues, _
Optional LookAt As XlLookAt = xlPart, _
Optional MatchCase As Boolean = False) As Range
Dim c As Range, FirstAddress As String
Set Find_All = Nothing
With Search_Range
Set c = .Find( _
What:=Find_Item, _
LookIn:=LookIn, _
LookAt:=LookAt, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=MatchCase, _
SearchFormat:=False) 'Delete this term for XL2000 and earlier
If Not c Is Nothing Then
Set Find_All = c
FirstAddress = c.Address
Do
Set Find_All = Union(Find_All, c)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
End Function
ajilejay
10-09-2016, 04:34 PM
Thank you very much you have been very helpful
=IF(COUNTIF('High Alert'!A:A,K2)>0,"High Alert","")
by other formula.
1)define name
name;
List_HighAlert
refers to :
=OFFSET('High Alert'!$A$1,0,0,COUNTA('High Alert'!$A:$A),1)
2)formula of L2
=IF(SUMPRODUCT(COUNTIF(F2,List_HighAlert&"*")),"High Alert","")
3)fill it down
shrivallabha
10-11-2016, 09:12 AM
In cell L2:
=IF(ISNA(LOOKUP(2,SEARCH('High Alert'!$A$2:$A$46,'Drug List'!F2,1))),"","High Alert")
copy down
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.