ajilejay
09-18-2016, 02:55 PM
Sub MatchAndReplace()
Dim ws As Worksheet
Dim arrKeysA As Variant, arrKeysB As Variant, arrData As Variant
Dim i As Integer, j As Integer, k As Integer
'-- here we take keys column A from Sheet 1 into a 1D array
arrKeysA = WorksheetFunction.Transpose(Sheets(1).Range("A1:A38").Value)
'-- here we take to be replaced range from Sheet 2 into a 2D array
arrData = WorksheetFunction.Transpose(Sheets(2).Range("A1:I100").Value)
'-- here we iterate through each key in keys array searching it in
'-- to-be-replaced array
For i = LBound(arrKeysA) To UBound(arrKeysA)
For j = LBound(arrData, 2) To UBound(arrData, 2)
'-- when there's a match we replace that element
If UCase(Trim(arrData(1, j))) = UCase(Trim(arrKeysA(i))) Then
arrData(1, j) = Trim(arrKeysB(i))
End If
'-- when there's a match we replace that element
If UCase(Trim(arrData(2, j))) = UCase(Trim(arrKeysA(i))) Then
arrData(2, j) = Trim(arrKeysB(i))
End If
Next j
Next i
'-- put new data on the sheet 3
Sheets(3).Range("A1").Offset(0, 0).Resize(UBound(arrData, 2), _
UBound(arrData)) = Application.Transpose(arrData)
End Sub
What this does. Compare sheet 1 to sheet 2 and transpose matches to sheet 3.
What I would like to have happen. Compare sheet 2 to sheet 1 and replace matches in sheet 1 with sheet 2 (case sensitive) as the index.
For example
Sheet 1
ESCITALOPRAM OXALATE 20 MG PO TABS
FAMOTIDINE 20 MG PO TABS
FERROUS SULFATE 325 (65 FE) MG PO TABS
FINASTERIDE 5 MG PO TABS
FISH OIL 1000 MG PO CAPS
FLUOXETINE HCL 20 MG PO CAPS
FOLIC ACID 1 MG PO TABS
FUROSEMIDE 20 MG PO TABS
FUROSEMIDE 40 MG PO TABS
FUROSEMIDE 80 MG PO TABS
GABAPENTIN 100 MG PO CAPS
Sheet 2
DULoxetine
ePHEDrine
EPINEPHrine
fentaNYL
flavoxATE
FLUoxetine
fluPHENAZine
fluvoxaMINE
guaiFENesin
guanFACINE
HumaLOG*
Result for Sheet 1(note:the only match being Fluoxetine)
ESCITALOPRAM OXALATE 20 MG PO TABS
FAMOTIDINE 20 MG PO TABS
FERROUS SULFATE 325 (65 FE) MG PO TABS
FINASTERIDE 5 MG PO TABS
FISH OIL 1000 MG PO CAPS
FLUoxetine HCL 20 MG PO CAPS
FOLIC ACID 1 MG PO TABS
FUROSEMIDE 20 MG PO TABS
FUROSEMIDE 40 MG PO TABS
FUROSEMIDE 80 MG PO TABS
GABAPENTIN 100 MG PO CAPS
How do I fix this :(?
Dim ws As Worksheet
Dim arrKeysA As Variant, arrKeysB As Variant, arrData As Variant
Dim i As Integer, j As Integer, k As Integer
'-- here we take keys column A from Sheet 1 into a 1D array
arrKeysA = WorksheetFunction.Transpose(Sheets(1).Range("A1:A38").Value)
'-- here we take to be replaced range from Sheet 2 into a 2D array
arrData = WorksheetFunction.Transpose(Sheets(2).Range("A1:I100").Value)
'-- here we iterate through each key in keys array searching it in
'-- to-be-replaced array
For i = LBound(arrKeysA) To UBound(arrKeysA)
For j = LBound(arrData, 2) To UBound(arrData, 2)
'-- when there's a match we replace that element
If UCase(Trim(arrData(1, j))) = UCase(Trim(arrKeysA(i))) Then
arrData(1, j) = Trim(arrKeysB(i))
End If
'-- when there's a match we replace that element
If UCase(Trim(arrData(2, j))) = UCase(Trim(arrKeysA(i))) Then
arrData(2, j) = Trim(arrKeysB(i))
End If
Next j
Next i
'-- put new data on the sheet 3
Sheets(3).Range("A1").Offset(0, 0).Resize(UBound(arrData, 2), _
UBound(arrData)) = Application.Transpose(arrData)
End Sub
What this does. Compare sheet 1 to sheet 2 and transpose matches to sheet 3.
What I would like to have happen. Compare sheet 2 to sheet 1 and replace matches in sheet 1 with sheet 2 (case sensitive) as the index.
For example
Sheet 1
ESCITALOPRAM OXALATE 20 MG PO TABS
FAMOTIDINE 20 MG PO TABS
FERROUS SULFATE 325 (65 FE) MG PO TABS
FINASTERIDE 5 MG PO TABS
FISH OIL 1000 MG PO CAPS
FLUOXETINE HCL 20 MG PO CAPS
FOLIC ACID 1 MG PO TABS
FUROSEMIDE 20 MG PO TABS
FUROSEMIDE 40 MG PO TABS
FUROSEMIDE 80 MG PO TABS
GABAPENTIN 100 MG PO CAPS
Sheet 2
DULoxetine
ePHEDrine
EPINEPHrine
fentaNYL
flavoxATE
FLUoxetine
fluPHENAZine
fluvoxaMINE
guaiFENesin
guanFACINE
HumaLOG*
Result for Sheet 1(note:the only match being Fluoxetine)
ESCITALOPRAM OXALATE 20 MG PO TABS
FAMOTIDINE 20 MG PO TABS
FERROUS SULFATE 325 (65 FE) MG PO TABS
FINASTERIDE 5 MG PO TABS
FISH OIL 1000 MG PO CAPS
FLUoxetine HCL 20 MG PO CAPS
FOLIC ACID 1 MG PO TABS
FUROSEMIDE 20 MG PO TABS
FUROSEMIDE 40 MG PO TABS
FUROSEMIDE 80 MG PO TABS
GABAPENTIN 100 MG PO CAPS
How do I fix this :(?