Return Value in column if two columns Contains Specific Text if specific text not found then whatever text is in cell copied into a different column

[Aggie04]

Hi everyone,

I am working on a project to clean up an excel spreadsheet. I have two columns H & I, that have doctor specialties. I need to run a search in these two columns and find the specific text "Internal medicine" if this value is found, I need to copy that text into Column J. If any other text is found in columns H&I, that text needs to go into column K.

This is what I have so far:

Sub copyif()
Dim SrchRng As Range, cel As Range Set SrchRng = Range("h2:i2") For Each cel In SrchRng If Range("H2").Value = "Internal Medicine" And Range("I2").Value = "Internal Medicine" Then

End Sub


I don't know if I am starting this code correctly and can't figure out the code's copy portion.

I was using: =IF(ISNUMBER(SEARCH function but I need to find text in two columns and return the value "Internal Medicine" into Column j if that specific text is found and if any other text is found in Columns H&I then that text needs to be copied into column K. I am guessing VBA code is the only way to accomplish this task.

 

[Fluff]

Will "Internal Medicine" be the only contents of the cell, or will there be other text with it?
Also does it have to be in both H & I or just one of them?
One other thing if the text is not found do cols H & I have to be concatenated into K, or will only one of the columns have data?

 

[Aggie04]

Will "Internal Medicine" be the only contents of the cell, or will there be other text with it?
Also does it have to be in both H & I or just one of them?
One other thing if the text is not found do cols H & I have to be concatenated into K, or will only one of the columns have data?

Columns H& I have all sorts of specialties sometimes internal medicine is attached with other specialties but if the term Internal Medicine is in either or of these two columns I need the text Internal medicine to show up in J.

any text that is not internal medicine would just go in column K.

 

[Fluff]

Does col K need to concatenate the text from H & I?

 

[Aggie04]

Does col K need to concatenate the text from H & I?

I would say yes.

 

[Fluff]

Ok, how about in J2

=IF(ISNUMBER(SEARCH("Internal medicine",H2&I2)),"Internal Medicine","")

and K2

=SUBSTITUTE(LOWER(H2&" " & I2),"internal medicine","")

 

[Aggie04]

Ok, how about in J2

=IF(ISNUMBER(SEARCH("Internal medicine",H2&I2)),"Internal Medicine","")

and K2

=SUBSTITUTE(LOWER(H2&" " & I2),"internal medicine","")

This was perfect! Thank you so much!!! I really do appreciate the help. THANK YOU THANK YOU!

 

[Fluff]

You're welcome & thanks for the feedback.