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:
<tbody>
</tbody>
and in sheet 2 I have data like this:
<tbody>
</tbody>
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.
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:
DIAGNOSIS | TYPE |
chronic renal disease nephrotic syndrome lupus erythematosus systemic nephritis lupus class vi glomorulonephritis membranoproliferative chronic kidney disease | |
non-hodgkin lymphoma bone marrow transplant | |
acute lymphoma leukemia |
<tbody>
</tbody>
and in sheet 2 I have data like this:
TYPE | DX |
B301 | conjunctivitis due to adenovirus |
B30X | cell lymphoma b, without other specification [stage 1] |
B303 | Chronic renal failure, unspecified the text in column DIAGNOSIS worksheet |
<tbody>
</tbody>
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.