Trying to use specific text strings in A:A to populate something else in B:B

[urodoc]

Hi. I've tried using VLOOKUP, but I guess that didn't work because I need full values and not parts.

Column A has text that I need to transcribe into Column B as a specific Category. For instance, for every time "RX" appears in column A (not standalone necessarily) I need Column B to write "Medicine." For every time "Operative" or "OP" appears in Column A, I need "OP Report" to show up in column B. Here's a small example of what my A and B should look like. As it stands, A is full and B is completely empty.

[TABLE="width: 500"]
<tbody>[TR]
[TD]AMBIENRX[/TD]
[TD]Medication[/TD]
[/TR]
[TR]
[TD]AMOXICILLIN RX PHARM[/TD]
[TD]Medication[/TD]
[/TR]
[TR]
[TD]SURGICAL PATH[/TD]
[TD]Pathology Report[/TD]
[/TR]
[TR]
[TD]SURGICAL PATH 7/11[/TD]
[TD]Pathology Report[/TD]
[/TR]
[TR]
[TD]PATIENT INTAKE[/TD]
[TD]Past Record[/TD]
[/TR]
[TR]
[TD]PAST RECORD 2/13[/TD]
[TD]Past Record[/TD]
[/TR]
[TR]
[TD]OP REPORT[/TD]
[TD]OP Report[/TD]
[/TR]
[TR]
[TD]CT CYSTOGRAM 3/05/13[/TD]
[TD]X-Ray[/TD]
[/TR]
</tbody>[/TABLE]

Any help is greatly appreciated, including letting me know that I'm asking for the moon!

Thanks a bunch

 

[urodoc]

Forgot to mention I'm in Excel 2010.

 

[Michael M]

Hi and welcome to the Board
I'm guessing this is only part of a bigger question....

So would a VBA soultion be suitable

Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 1 Step -1
    If InStr(Range("A" & r).Value, "RX") Then
        Range("B" & r).Value = "Medicine"
    ElseIf InStr(Range("A" & r).Value, "OP") Then
        Range("B" & r).Value = "OP Report"
    End If
Next r
End Sub

 

[bschwartz]

	A	B
AMBIENRX	Medication
AMOXICILLIN RX PHARM	Medication
SURGICAL PATH	Pathology Report
SURGICAL PATH 7/11	Pathology Report
PATIENT INTAKE
PAST RECORD 2/13	Past Record
OP REPORT	OP Report
CT CYSTOGRAM 3/05/13

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

</tbody>

Formula: =IF(ISNUMBER(SEARCH("RX",A1)),"Medication",IF(OR(ISNUMBER(SEARCH({"Operative";"Op"},A1))),"OP Report",IF(ISNUMBER(SEARCH("Path",A1)),"Pathology Report",IF(ISNUMBER(SEARCH("Past",A1)),"Past Record",""))))

I dont know how to find "Past Record" from Patient Intake or "X-Ray" from CT CYSTOGRAM 3/05/13. If you have a criteria for that, I can add it to the formula.