find matching words within two text

[Paterson]

Hi dear friends!
Hope you all doing great.
I am working on a spreadsheet I stuck on a functionality that i want to implement.

Here is the scenario.
I have a sheet1 with some data like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]DIAGNOSIS[/TD]
[TD]TYPE[/TD]
[/TR]
[TR]
[TD]chronic renal disease nephrotic syndrome lupus erythematosus systemic nephritis lupus class vi glomorulonephritis membranoproliferative chronic kidney disease[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]non-hodgkin lymphoma bone marrow transplant[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]acute lymphoma leukemia[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

and in sheet 2 I have data like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]TYPE[/TD]
[TD]DX[/TD]
[/TR]
[TR]
[TD]B301[/TD]
[TD]conjunctivitis due to adenovirus[/TD]
[/TR]
[TR]
[TD]B30X[/TD]
[TD]cell lymphoma b, without other specification [stage 1][/TD]
[/TR]
[TR]
[TD]B303[/TD]
[TD]Chronic renal failure, unspecified the text in column DIAGNOSIS worksheet[/TD]
[/TR]
</tbody>[/TABLE]

Well! now what I want to do is comparing the text of the column DIAGNOSIS of sheet1 to the text of the column DX of sheet2. If i find some word that match within the text of two cells, I want to get the type from sheet2 and brings it to sheet1.

For example:
In sheet1!A2 I have the text:
chronic renal disease nephrotic syndrome lupus erythematosus systemic nephritis lupus class vi glomorulonephritis membranoproliferative chronic kidney disease.

In sheet2!B4 I have the text:
Chronic renal failure, unspecified the text in column DIAGNOSIS worksheet

Now within these two text, the words Chronic renal is matched. So I want get the type from sheet2!B4 and put it in sheet1!B1.

NB: These two words are not always in the beginning of the two text.
Example:
text1: acute lymphoma leukemia
text2: cell lymphoma b, without other specification [stage 1]
Now lymphoma is the matching word.

So I wonder if there is a combination of formula to do this or if someone can please help with an example of a VBA code.

Thanks in advance.

 

[MickG]

Try this:-
NB:- The code will supply the matching Code Number in Sheet1 column "B" and the matching word in column "C" to give you some idea of how the code is working
Remove this line (marked) in code if not required.

[COLOR=navy]Sub[/COLOR] MG02Jun35
[COLOR=navy]Dim[/COLOR] Rng1 [COLOR=navy]As[/COLOR] Range, Dn1 [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Rng2 [COLOR=navy]As[/COLOR] Range, Dn2 [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Sp1 [COLOR=navy]As[/COLOR] Variant, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] Sp2 [COLOR=navy]As[/COLOR] Variant, nn [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]With[/COLOR] Sheets("Sheet1")
    [COLOR=navy]Set[/COLOR] Rng1 = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]End[/COLOR] With
[COLOR=navy]With[/COLOR] Sheets("Sheet2")
    [COLOR=navy]Set[/COLOR] Rng2 = .Range("B2", .Range("B" & Rows.Count).End(xlUp))
[COLOR=navy]End[/COLOR] With
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn1 [COLOR=navy]In[/COLOR] Rng1
    Sp1 = Split(Dn1.Value, " ")
    [COLOR=navy]For[/COLOR] nn = 0 To UBound(Sp1)
        [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn2 [COLOR=navy]In[/COLOR] Rng2
            Sp2 = Split(Dn2.Value, " ")
                [COLOR=navy]For[/COLOR] n = 0 To UBound(Sp2)
                    [COLOR=navy]If[/COLOR] UCase(Sp1(nn)) = UCase(Sp2(n)) [COLOR=navy]Then[/COLOR]
                        Dn1.Offset(, 1) = Dn2.Offset(, -1)
                            '[COLOR=green][B]Remove line below if not required.[/B][/COLOR]
                            Dn1.Offset(, 2) = Dn1.Offset(, 2) & ", " & Sp2(n)
                    [COLOR=navy]End[/COLOR] If
                [COLOR=navy]Next[/COLOR] n
        [COLOR=navy]Next[/COLOR] Dn2
    [COLOR=navy]Next[/COLOR] nn
[COLOR=navy]Next[/COLOR] Dn1
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[Paterson]

Try this:-NB:- The code will supply the matching Code Number in Sheet1 column "B" and the matching word in column "C" to give you some idea of how the code is working Remove this line (marked) in code if not required.

[COLOR=navy]Sub[/COLOR] MG02Jun35[COLOR=navy]Dim[/COLOR] Rng1 [COLOR=navy]As[/COLOR] Range, Dn1 [COLOR=navy]As[/COLOR] Range[COLOR=navy]Dim[/COLOR] Rng2 [COLOR=navy]As[/COLOR] Range, Dn2 [COLOR=navy]As[/COLOR] Range[COLOR=navy]Dim[/COLOR] Sp1 [COLOR=navy]As[/COLOR] Variant, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR][COLOR=navy]Dim[/COLOR] Sp2 [COLOR=navy]As[/COLOR] Variant, nn [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR][COLOR=navy]With[/COLOR] Sheets("Sheet1")    [COLOR=navy]Set[/COLOR] Rng1 = .Range("A2", .Range("A" & Rows.Count).End(xlUp))[COLOR=navy]End[/COLOR] With[COLOR=navy]With[/COLOR] Sheets("Sheet2")    [COLOR=navy]Set[/COLOR] Rng2 = .Range("B2", .Range("B" & Rows.Count).End(xlUp))[COLOR=navy]End[/COLOR] With[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn1 [COLOR=navy]In[/COLOR] Rng1    Sp1 = Split(Dn1.Value, " ")    [COLOR=navy]For[/COLOR] nn = 0 To UBound(Sp1)        [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn2 [COLOR=navy]In[/COLOR] Rng2            Sp2 = Split(Dn2.Value, " ")                [COLOR=navy]For[/COLOR] n = 0 To UBound(Sp2)                    [COLOR=navy]If[/COLOR] UCase(Sp1(nn)) = UCase(Sp2(n)) [COLOR=navy]Then[/COLOR]                        Dn1.Offset(, 1) = Dn2.Offset(, -1)                            '[COLOR=green][B]Remove line below if not required.[/B][/COLOR]                            Dn1.Offset(, 2) = Dn1.Offset(, 2) & ", " & Sp2(n)                    [COLOR=navy]End[/COLOR] If                [COLOR=navy]Next[/COLOR] n        [COLOR=navy]Next[/COLOR] Dn2    [COLOR=navy]Next[/COLOR] nn[COLOR=navy]Next[/COLOR] Dn1[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

Wow! Thousands of Thanks Mick. It works great homie. God bless you!

 

[Peter_SSs]

It works great homie.

Without criticising Mick's code (as I had looked at this problem before and couldn't come up with a good solution. ), I would raise a few potential problems with the code, and really with your whole concept of just looking for common words. I shall do so through examples.

Suppose Sheet2 is exactly as shown in your first post and Sheet1 contained cells with the following texts.

1. Sheet1 contains "some medical terms including chronic back pain"
The code returns B303 because of the common word "chronic" even though, to me, one sheet really has "chronic renal failure" & the other has the unrelated "chronic back pain"

2. Sheet1 contains "kidney failure"
The code returns nothing despite "failure" being a common word (in B303). (The reason being the comma following the word in Sheet2.)

3. Sheet1 contains "death due to cardiac arrest"
The code returns B301 even though this clearly has nothing to do with "conjunctivitis due to adenovirus", but results from the common, non-medical, words "due" and "to"

4. Sheet1 contains "chronic renal failure and other symptoms"
The code returns B30X due to the positioning of the non-medical word "other" when the Sheet1 text includes the exact longer medical phrase from B303 in Sheet2: "chronic renal failure"

 

[Paterson]

Hi Peter!
Great idea.
I saw this same problem too, I just needed an example so that I could modify it myself to get the exact result that I need. But I looked at Mick's code, I understand it, but still can't find a better solution.
The only way I think that I could get a better result is by making a list of all the medical terms that I need to look up and then write the code based on the words of this list.
But Mick did a great work by supplying this example.

Thanks for your review and please let me know if you ever find a better solution as I am still thinking about it.
Take care,

 

[Peter_SSs]

The only way I think that I could get a better result is by making a list of all the medical terms that I need to look up and then write the code based on the words of this list.

'Medical terms' would be a much better idea, eliminating common, non-medical words. So a list of medical terms, separated by, say, a comma may prove better. However, I'm not sure about what you need. For example, if B303 was "chronic renal failure" and the other sheet contained "nephrotic syndrome, chronic renal disease, lupus" would you still expect a match given that "chronic renal failure" is not quite the same as "chronic renal disease"?