Find specific words in another sheet, return lookup

[joand]

I have 2 worksheets ("database", and "source").

The sheet "database" contains two columns ("A" and "B") where all disease names are listed in column "A" and their treatment (or pharmaceutical drugs) are listed in Column B.

The other worksheet "source" contains one column ("A") where "A1:A15" contains rows of paragraphs. I want to search each item (or row) or disease name in the sheet "source" and if the disease is found, returns the value of pharmaceutical drug that will be used to treat the disease in cell B1, separated by semi-colon for each succeeding drug/treatment.

For example, in sheet "database" A1:=Cholera, B1:=oral rehydration therapy, A2:=Diphtheria, B2:=Metronidazole. I want to search the diseases Cholera and Diphtheria in the "source" worksheet. The source sheet is in paragraph form. If it finds both diseases, then B1 value is "oral rehydration therapy: Metronidazole". The disease list (words to search) in column A number around 100+.

Anyone has an idea?

 

[hiker95]

joand,

Can we see some of the paragraphs in worksheet source?


What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

 

[Akashwani]

Hi joand,

I think that you need a VBA solution to your problem, unless you are happy having the data returned in separate columns...

Excel Workbook
	A	B
1	Name	Data1
2	Cat	Red
3	Dog	Green
4	Horse	Blue
5	Mouse	Black
6	Pig	White
7	Bull	Yellow
Database

Excel Workbook
	A	B	C	D	E	F
1	Phrase	Count	Data1
2	Cat in a hat	1	Red	*	*	*
3	Hat with a cat inside	1	Red	*	*	*
4	Dilbert the dog	1	Green	*	*	*
5	Dog and Cat	2	Red	Green	*	*
6	House with a mouse	1	Black	*	*	*
7	Angry bull	1	Yellow	*	*	*
8	House with a mouse with a cat and Dog	3	Red	Green	Black	*
Source

The formula in B2 needs to be copied down.
The formula in C2 needs entering with ctrl shift enter NOT enter.
You can then copy that formula across and down.

I hope that helps you.

Ak

 

[hiker95]

joand,


Sample worksheets:

Excel Workbook
	A	B
1	Cholera	oral rehydration therapy
2	Diphtheria	Metronidazole
3
database

Excel Workbook
	A	B
1	search the diseases Cholera and Diphtheria
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
source

After the macro:

Excel Workbook
	A	B
1	search the diseases Cholera and Diphtheria	oral rehydration therapy: Metronidazole
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
source

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Option Base 1
Sub GetTreatment()
' hiker95, 06/12/2011
' http://www.mrexcel.com/forum/showthread.php?t=556597
Dim ws As Worksheet, wd As Worksheet
Dim a As Long, aa As Long, H As String
Dim D() As Variant, S() As Variant
Application.ScreenUpdating = False
Set ws = Worksheets("source")
Set wd = Worksheets("database")
D = wd.UsedRange.Value
S = ws.Range("A1:B15").Value
For a = LBound(S) To UBound(S)
  H = ""
  For aa = LBound(D) To UBound(D)
    If InStr(S(a, 1), D(aa, 1)) > 0 Then
      H = H & D(aa, 2) & ": "
    End If
  Next aa
  If H <> "" Then
    If Right(H, 2) = ": " Then H = Left(H, Len(H) - 2)
    S(a, 2) = H
  End If
Next a
ws.Range("A1:B15").Value = S
ws.UsedRange.Columns.AutoFit
ws.Activate
Erase D: Erase S
Application.ScreenUpdating = True
End Sub

Then run the GetTreatment macro.

 

[joand]

thanks hiker! great work. thank u too akashwani.

 

[joand]

Hi hiker:

How do I change this macro so that it won't be case sensitive. The problem is when I input "cholera" and "diptheria" in the "source" sheet it does not return the treatment value since it only "knows" Cholera and Diptheria (proper case)

 

[hiker95]

joand,


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub GetTreatmentV3()
' hiker95, 06/14/2011
' http://www.mrexcel.com/forum/showthread.php?t=556597
Dim a As Long, aa As Long, H As String
Dim D() As Variant, S() As Variant
D = Worksheets("database").UsedRange.Value
S = Worksheets("source").Range("A1:B15").Value
For a = LBound(S) To UBound(S)
  H = ""
  For aa = LBound(D) To UBound(D)
    If InStr(LCase(S(a, 1)), LCase(D(aa, 1))) > 0 Then
      H = H & D(aa, 2) & ": "
    End If
  Next aa
  If H <> "" Then
    If Right(H, 2) = ": " Then H = Left(H, Len(H) - 2)
    S(a, 2) = H
  End If
Next a
Worksheets("source").Range("A1:B15").Value = S
Worksheets("source").Columns(2).AutoFit
Erase D: Erase S
End Sub

Then run the GetTreatmentV3 macro.

 

[joand]

thanks again Hiker. u made my day

 

[hiker95]

joand,

Thanks for the feedback.

Glad I could help.

Come back anytime.