search for text in string to find matches...

[ronmaltase]

Hello,

I’ve got two sheets with columns of data. I need a formula to look at each word in each cell in column c on sheet one, and then search each word in each cell in column e on sheet two, and if it finds a matching word, then return the corresponding value from sheet two column f back to sheet one column d.


I’ve used this before: IF(ISNUMBER(SEARCH("OVERTIME",L3)),"OVERTIME",””)


But that is searching for text that I’ve specified, from a specific cell. I’ve got two sheets ‘and’ ranges of data.


Any thoughts? Thanks, Ron M.

 

[SirJB7]

Hi, ronmaltase!


In D2 of 1st worksheet place this formula and copy down as needed:

=SI.ERROR(BUSCARV(C2;Hoja2!E:F;2;FALSO);"") -----> in english: =IFERROR(VLOOKUP(C2,Hoja2!E:F,2,FALSE),"")


Regards!

 

[ronmaltase]

Thank you SirJB7.


I was not able to get that formual to work. With or without converting it to an array (ctrl+shift+enter). This formula returned NO for all lines. There are matches in both lists, it should find them.


IFERROR(VLOOKUP(C2,'IV FLUID BAR CDM TABLE'!$E$2:$F$26,2,FALSE),"NO")


I've tried ISNUMBER, COUNTIF, FIND, COUNT, LOOKUP, IFERROR, and INDEX/MATCH, formulas.


I'm trying to look at a cell that has the word RING, and then search a cell on sheet two for RING. If I use the excel find function to seach for RING, it finds all occurrences of RING, like RING, SYRINGE, SUTURING. Why can't I find a formula to do this same thing???


-Thanks, Ron M.

 

[SirJB7]

Hi, ronmaltase!

Consider uploading a sample file (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.

Regards!

 

[NARAYANK991]

Hi Ron ,


I am not sure I have understood your problem , but is it similar to this ?


http://chandoo.org/forums/topic/vlookup-wild-cards


Narayan

 

[ronmaltase]

It looks like the issue i'm running into with this is that the cell I'm using as my source has more than one word in it.

If cell c2 'only' has the single word "ring" these formulas work if the TABLE ARRAY 'only' has the single word 'ring'. But it c2 has 'ring a ding ding' then the formulas do not work??? I hope I don't have to parse out all of the words in c2???


VLOOKUP(C2,'IV FLUID BAR CDM TABLE'!$E$2:$F$26,2,FALSE


VLOOKUP("* *"&C2&"* *",'IV FLUID BAR CDM TABLE'!$E$2:$F$26,2,FALSE)


LOOKUP(2,1/ISNUMBER(SEARCH(C2,'IV FLUID BAR CDM TABLE'!$E$2:$E$26)),'IV FLUID BAR CDM TABLE'!$F$2:$F$26)


any thoughts??


-Thanks, Ron M.

 

[SirJB7]

Hi, ronmaltase!


Let me see if I understand the new scenario:

a) You have worksheet with a column with words or phrases (source: C in 1st sheet)

b) In the same sheet you have a column where you want to retrieve something upon source value (target: column D in 1st sheet)

c) You have another worksheet with a column with words where you want to perform a search among them for each word of source cells (dictionary: E in 2nd sheet)

d) In the same sheet you have a column with something you want to retrieve (attribute: F in 2nd sheet)


If this is right, these are the questions:

1) If any source cell contains more than one word that exists in dictionary, what to retrieve?


If I didn't get the point, please go to this:

http://chandoo.org/forums/topic/search-for-text-in-string-to-find-matches#post-102940


Regards!

 

[SirJB7]

Hi, ronmaltase!


Check if this works for you:

-----

[pre]

Option Explicit

Sub Wording()
' constants
Const ksWSPhrase = "Hoja1"
Const ksPhrase = "PhraseTable"
Const ksWSDictionary = "Hoja2"
Const ksDictionary = "DictionaryTable"
' declarations
'  ranges
Dim rngP As Range, rngD As Range
'  others
Dim I As Long, J As Integer
Dim iWords As Integer, iFrom As Integer, iTo As Integer, lRow As Long
Dim sPhrase As String, sWord As String, sAttributes As String
' start
Set rngP = Worksheets(ksWSPhrase).Range(ksPhrase)
Set rngD = Worksheets(ksWSDictionary).Range(ksDictionary)
' process
With rngP
For I = 1 To .Rows.Count
sPhrase = Trim(.Cells(I, 1).Value) & Space(1)
iWords = Len(sPhrase) - Len(Replace(sPhrase, Space(1), ""))
iFrom = 1
sAttributes = ""
For J = 1 To iWords
iTo = InStr(iFrom + 1, sPhrase, Space(1))
sWord = Mid(sPhrase, iFrom, iTo - iFrom)
lRow = 0
On Error Resume Next
lRow = rngD.Columns(1).Find(sWord, , , xlWhole).Row
On Error GoTo 0
If lRow <> 0 Then _
sAttributes = sAttributes & rngD.Cells(lRow - rngD.Row + 1, 2).Value & Space(1)
iFrom = iTo + 1
Next J
.Cells(I, 2).Value = Trim(sAttributes)
Next I
End With
' end
Set rngD = Nothing
Set rngP = Nothing
Beep
End Sub

[/pre]
-----


Define dynamic ranges PhrasesTable and DictionaryTable properly.


Regards!