find data containing key words

NKUNZI

New Member
Joined
Feb 8, 2019
Messages
13
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Is there a way of extracting data from another sheet to be placed in the current sheet based on certain key words.
in short I have two sets of data that indicate the same thing but does not match exactly, and I would like to pull in the data based on key words.
to give an example:-
the list that i want to copy in the new sheet looks like this
Abacavir; 60mg; Tablet, dispersible; 56 Tablets
the list that I want to use to pull this in is
Abacavir 60mg Tablets; 56 [po]
the thinking was that if I extract up to Abacavir 60mg, then use that as the key I could then find
Abacavir; 60mg; Tablet, dispersible; 56 Tablets in the other sheet, copy and paste it next to
the data in the current sheet
Vlookup, or index match doesn't cut it in this instance
The lookup list contains around 500 entries, and the list being used for key words varies from around 10 to 90

Hope I have explained clearly what I am looking for
in text format it translates as such:-
find in sheet 1 the text that matches best with the selected text found in sheet 2
once found copy the text from sheet 1 into the column next to the text used for the search from sheet 2
Ultimately this would be used in index(match) to extract data into the adjacent columns

Thank you
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Hi NKUNZI. This would be fairly easy to do with VBA but is seems that you want a formula approach. You need to share exactly where (columns and rows and sheet) the search data is and exactly where (columns and rows and sheet) you want the output. I'm sure then that someone will be able to come up with some clever formula approach. HTH. Dave
 
Upvote 0
Hi NKUNZI. This would be fairly easy to do with VBA but is seems that you want a formula approach. You need to share exactly where (columns and rows and sheet) the search data is and exactly where (columns and rows and sheet) you want the output. I'm sure then that someone will be able to come up with some clever formula approach. HTH. Dave
Don't mind formula or vba. but if using formula, then would look at placing the data starting at U2 in a worksheet named PRODUCT DATA
Number of rows varies according to the data captured but should in all likelihood not exceed 1000 lines
The data used for the search is contained in column T starting at T2 in PRODUCT DATA and the array that the search is being conducted on is found in a sheet labelled MMDS SHEET running from C1 (heading) to C2300. The data from this sheet (MMDS SHEET) once matched is what is copied to U2 onwards in PRODUCT DATA
 
Upvote 0
If you want to transfer the found cell downwards to C2300 from sheet MMDS to U2 of Product data this will do it. Dave
Code:
Sub test()
Dim LastRow As Integer, Cnt As Integer, Cnt2 As Integer, Rng As Range
With Sheets("Product Data")
LastRow = .Range("T" & .Rows.Count).End(xlUp).Row
End With
For Cnt = 2 To LastRow
For Cnt2 = 2 To 2300
If Sheets("Product Data").Range("T" & Cnt) = Sheets("MMDS Sheet").Cells(Cnt2, 3) Then
With Sheets("MMDS Sheet")
Set Rng = .Range(.Cells(Cnt2, 3), .Cells(2300, 3))
End With
With Sheets("Product Data")
.Cells(2, "U").Resize(Rng.Rows.Count, Rng.Columns.Count).Cells.Value = Rng.Cells.Value
End With
Exit Sub
End If
Next Cnt2
Next Cnt
End Sub
 
Upvote 0
What data is copied from MMDS SHEET to PRODUCT DATA? Dave
The data that would match the search item found in product data. Using the example above in Product Data the item is
Abacavir 60mg Tablet; 56 Tablet [PO]. If one used Index(Match) this would be the match
This is used based on the key words Abacavir 60mg Tablet obtained by using the left function with length find ";"
using the match, the index would be from MMDS SHEET column C:C among which would be Abacavir; 60mg; Tablet, dispersible; 56 Tablets
so the same thing in essence but variations in text. This is the text that I would like copied from the MMDS sheet into the Product data sheet in the column next to
Abacavir 60mg Tablet; 56 Tablet [PO]
 
Upvote 0
If you want to transfer the found cell downwards to C2300 from sheet MMDS to U2 of Product data this will do it. Dave
Code:
Sub test()
Dim LastRow As Integer, Cnt As Integer, Cnt2 As Integer, Rng As Range
With Sheets("Product Data")
LastRow = .Range("T" & .Rows.Count).End(xlUp).Row
End With
For Cnt = 2 To LastRow
For Cnt2 = 2 To 2300
If Sheets("Product Data").Range("T" & Cnt) = Sheets("MMDS Sheet").Cells(Cnt2, 3) Then
With Sheets("MMDS Sheet")
Set Rng = .Range(.Cells(Cnt2, 3), .Cells(2300, 3))
End With
With Sheets("Product Data")
.Cells(2, "U").Resize(Rng.Rows.Count, Rng.Columns.Count).Cells.Value = Rng.Cells.Value
End With
Exit Sub
End If
Next Cnt2
Next Cnt
End Sub
This copies all the data as is but does not align to items of similar description
 
Upvote 0
Trial 2....
Code:
Sub test()
Dim LastRow As Integer, Cnt As Integer, Cnt2 As Integer, Splitter As Variant
With Sheets("Product Data")
LastRow = .Range("T" & .Rows.Count).End(xlUp).Row
End With
For Cnt = 2 To LastRow
For Cnt2 = 2 To 2300
Splitter = Split(Sheets("Product Data").Range("T" & Cnt), ";")
If InStr(Splitter(0), Sheets("MMDS Sheet").Cells(Cnt2, 3)) Then
Sheets("Product Data").Range("U" & Cnt) = Sheets("MMDS Sheet").Cells(Cnt2, 3)
End If
Next Cnt2
Next Cnt
End Sub
Dave
 
Upvote 0
Well scratch that last one... too simplistic. As long as your formatting is as you posted and constant, my trials seems to indicate this will work. Dave
Trial 3...
Code:
Sub test()
Dim LastRowT As Integer, LastRowC As Integer, Cnt As Integer, Cnt2 As Integer
Dim Splitter As Variant, Splitter2 As Variant
With Sheets("Product Data")
LastRowT = .Range("T" & .Rows.Count).End(xlUp).Row
End With
With Sheets("MMDS Sheet")
LastRowC = .Range("C" & .Rows.Count).End(xlUp).Row
End With
For Cnt = 2 To LastRowT
For Cnt2 = 2 To LastRowC '2300
'split product at " "
Splitter = Split(Sheets("Product Data").Range("T" & Cnt), " ")
'split MSDS at ";"
Splitter2 = Split(Sheets("MMDS Sheet").Cells(Cnt2, 3), ";")
'compare drug
If InStr(Sheets("MMDS Sheet").Cells(Cnt2, 3), Splitter(0)) Then
'compare dose
If Splitter(1) = Right(Splitter2(1), Len(Splitter2(1)) - 1) Then
'compare quantity and distribution
If Splitter(3) & " " & Splitter(4) = Right(Left(Splitter2(3), Len(Splitter2(3)) - 1), _
                                    Len(Left(Splitter2(3), Len(Splitter2(3)) - 1)) - 1) Then
Sheets("Product Data").Range("U" & Cnt) = Sheets("MMDS Sheet").Cells(Cnt2, 3)
End If
End If
End If
Next Cnt2
Next Cnt
End Sub
 
Upvote 0
Thank you,
tried running it and encountered this error:-
1709817663116.png1709817771905.png
1709817771905.png

1709817663116.png
 
Upvote 0

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top