find data containing key words

[NKUNZI]

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

 

[NdNoviceHlp]

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

 

[NKUNZI]

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

 

[NdNoviceHlp]

What data is copied from MMDS SHEET to PRODUCT DATA? Dave

 

[NdNoviceHlp]

If you want to transfer the found cell downwards to C2300 from sheet MMDS to U2 of Product data this will do it. Dave

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

 

[NKUNZI]

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]

 

[NKUNZI]

If you want to transfer the found cell downwards to C2300 from sheet MMDS to U2 of Product data this will do it. Dave

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

 

[NdNoviceHlp]

Trial 2....

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

 

[NdNoviceHlp]

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...

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

 

[NKUNZI]

Thank you,
tried running it and encountered this error:-