Hi

I'm well out of my league on this one but its something that I really
need to do and was hoping someone could help

I have a column called Screener and each cell in that column contains
text data such as the following example

S1 ~ The specimen is satisfactory for evaluation. ~ 01;G1 ~ Negative
for intraepithelial lesion or malignancy. ~ 01;R1 ~ The next smear
should be taken at the usual screening interval. ~ 01

Another cell might contain different text

I cant change this as it is generated by software that I use and I
export that data to a spreadsheet.

What I need to do is run a macro to look at that text above along the
lines of - if the text in that cell contains the word S1 or the word
ASCUS or any other combination of words, then it will assign a text
value to it such as 'LG'. If it found some other word in that text
above then it might assign a different text type e.g if it found the
word HS1 then it would assign a value of 'HG'. It would put that
assigned value into a new column. It would then move to the next cell
in that column and perform the same function and so on.

So essentially I would like to compress all that text above into one
word depending on what key words it finds in that text. I have upwards
of several hundred records which I have to manually determine whether
its and LG or HG or something else and it would make life so much
easiier if I could read one word rather than a whole string of text. I
can then sort the data based on that new word it generated. Hope I've
made that clear. I have attached an example of what I'm trying to
acheive.

Any help would be most appreciated - I just dont have the necessary
skills to do this and was hoping someone out there would know the code
I would need to enter into the macro to acheive this end. Someone did
suggest an array formula but I would much rather stick with a macro as
I have less a grasp with formulae than I do with VBA.

kind regards

Steve


+-------------------------------------------------------------------+
|Filename: example.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4748 |
+-------------------------------------------------------------------+

--
ghobbit
------------------------------------------------------------------------
ghobbit's Profile: http://www.excelforum.com/member.php...o&userid=12385
View this thread: http://www.excelforum.com/showthread...hreadid=541209

I didn't look at your zip file (call me paranoid) so I hope this i
enough to get you started. It's probably not the most efficient but i
works.

Sub Test()
Dim Screener As String
Screener = "C" 'Change this to the proper column

Dim Criteria1 As String
Dim Criteria2 As String
Dim Criteria3 As String
Criteria1 = "S1" 'Change as needed
Criteria2 = "ASCUS" 'Change as needed
Criteria3 = "HS1" 'Change as needed
'(you can add more criterias if needed)
'Note: "HS1" criteria comes AFTER "S1" not before
'Note: search is Case Sensitive

Range(Screener & "2").Select 'Assumes that you have a header in Row 1

Dim iRow As Integer
Dim iTotalRows As Integer
iRow = 0
iTotalRows = ActiveSheet.UsedRange.Rows.Count

Do
If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria1 & "*" The
ActiveCell.Offset(iRow, 1).Value = "LG"
If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria2 & "*" The
ActiveCell.Offset(iRow, 1).Value = "LG"
If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria3 & "*" The
ActiveCell.Offset(iRow, 2).Value = "HG"
iRow = iRow + 1
Loop Until iRow = iTotalRows - 1

End Sub

ghobbit Wrote:
Hi

I'm well out of my league on this one but its something that I reall
need to do and was hoping someone could help

I have a column called Screener and each cell in that column contain
text data such as the following example

S1 ~ The specimen is satisfactory for evaluation. ~ 01;G1 ~ Negativ
for intraepithelial lesion or malignancy. ~ 01;R1 ~ The next smea
should be taken at the usual screening interval. ~ 01

Another cell might contain different text

I cant change this as it is generated by software that I use and
export that data to a spreadsheet.

What I need to do is run a macro to look at that text above along th
lines of - if the text in that cell contains the word S1 or the wor
ASCUS or any other combination of words, then it will assign a tex
value to it such as 'LG'. If it found some other word in that tex
above then it might assign a different text type e.g if it found th
word HS1 then it would assign a value of 'HG'. It would put tha
assigned value into a new column. It would then move to the next cel
in that column and perform the same function and so on.

So essentially I would like to compress all that text above into on
word depending on what key words it finds in that text. I have upward
of several hundred records which I have to manually determine whethe
its and LG or HG or something else and it would make life so muc
easiier if I could read one word rather than a whole string of text.
can then sort the data based on that new word it generated. Hope I'v
made that clear. I have attached an example of what I'm trying t
acheive.

Any help would be most appreciated - I just dont have the necessar
skills to do this and was hoping someone out there would know the cod
I would need to enter into the macro to acheive this end. Someone di
suggest an array formula but I would much rather stick with a macro a
I have less a grasp with formulae than I do with VBA.

kind regards

Stev

--
Ikaabo
-----------------------------------------------------------------------
Ikaabod's Profile: http://www.excelforum.com/member.php...fo&userid=3337
View this thread: http://www.excelforum.com/showthread.php?threadid=54120

Hi

Many thanks for that - I'll have a play with it shortly.

At the risk of appearing a bit cheeky though can I ask another thing
In the attachment I posted (I understand you not wanting to open it) i
had one example of where a cell contains the text ;;;LSIL- CIN1/HP
(with all the semi colons). I assume from your code that it'll be abl
to see LSIL which is the bit I'd be interested in and do what I wante
it to do.

However in the cell below it is another text which is ;;;LSIL
CIN1/HPV;ASC-H

ASC-H has a higher priority than LSIL (they're in reference to types o
cancers) so that would have to take preference. Is it possible t
manipulate the code in some way so that if it sees one thing such a
LSIL it would assign one code such as LG (low Grade) but if it sees
combination with both LSIL AND ASC-H (High Grade) then ASC-H would tak
preference and HG would duly be assigned into the column.

Sounds a bit complicated. But I need some flexibility to be able to ad
and change things as necessary. I cant write VBA (well not very much
but I can generally read it and get a feel for whats going on and I'l
have a play with your code that you've posted.

Many thanks for that - most appreciated

stev

--
ghobbi
-----------------------------------------------------------------------
ghobbit's Profile: http://www.excelforum.com/member.php...fo&userid=1238
View this thread: http://www.excelforum.com/showthread.php?threadid=54120

It will indeed find the LSIL.
-In the attachment I posted (I understand you not wanting to open it
it had one example of where a cell contains the text ;;;LSIL- CIN1/HP
(with all the semi colons). I assume from your code that it'll be abl
to see LSIL which is the bit I'd be interested in and do what I wante
it to do.-

in the code:
-Do
If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria1 & "*" The
ActiveCell.Offset(iRow, 1).Value = "LG"
If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria2 & "*" The
ActiveCell.Offset(iRow, 1).Value = "LG"
If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria3 & "*" The
ActiveCell.Offset(iRow, 2).Value = "HG"
iRow = iRow + 1
Loop Until iRow = iTotalRows - 1-each if/statement wil
overwrite the ones above it. So if the cell contains Criteria1 an
Criteria3, the results will be Criteria3 (i.e. "HG").
-However in the cell below it is another text which is ;;;LSIL
CIN1/HPV;ASC-H
ASC-H has a higher priority than LSIL (they're in reference to types o
cancers) so that would have to take preference. Is it possible t
manipulate the code in some way so that if it sees one thing such a
LSIL it would assign one code such as LG (low Grade) but if it sees
combination with both LSIL AND ASC-H (High Grade) then ASC-H would tak
preference and HG would duly be assigned into the column.-

I hope that helps. Let me know.
-Ikaabo

--
Ikaabo
-----------------------------------------------------------------------
Ikaabod's Profile: http://www.excelforum.com/member.php...fo&userid=3337
View this thread: http://www.excelforum.com/showthread.php?threadid=54120

Hi

Many thanks for your help and explainations.

I tweaked the code a little as per the instructions you included with
it and it works more or less as I had hoped it would. Except for a
couple of things - here is the code as I have it at the moment

Sub Test()
Dim Screener As String
Screener = "A" 'Change this to the proper column

Dim Criteria1 As String
Dim Criteria2 As String
Dim Criteria3 As String
Dim Criteria4 As String
Dim Criteria5 As String

Criteria1 = "LSIL" 'Change as needed
Criteria2 = "ASC-US" 'Change as needed
Criteria3 = "ASC-H" 'Change as needed
Criteria4 = "HSIL" 'Change as needed
Criteria5 = "G1" 'Change as needed


'(you can add more criterias if needed)
'Note: "HS1" criteria comes AFTER "S1" not before
'Note: search is Case Sensitive

Range(Screener & "2").Select 'Assumes that you have a header in Row 1

Dim iRow As Integer
Dim iTotalRows As Integer
iRow = 0
iTotalRows = ActiveSheet.UsedRange.Rows.Count

Do
If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria1 & "*" Then
ActiveCell.Offset(iRow, 1).Value = "LG"
If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria2 & "*" Then
ActiveCell.Offset(iRow, 1).Value = "LG"
If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria3 & "*" Then
ActiveCell.Offset(iRow, 2).Value = "HG"
If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria4 & "*" Then
ActiveCell.Offset(iRow, 1).Value = "HG"
If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria5 & "*" Then
ActiveCell.Offset(iRow, 1).Value = "NEG"

‘If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria6 & "*" Then
ActiveCell.Offset(iRow, 2).Value = "HG"

iRow = iRow + 1
Loop Until iRow = iTotalRows - 1

End Sub


I noticed where it says
Range(Screener & "2").Select 'Assumes that you have a header in Row 1

I had a header and when I ran the macro it did its job down to all but
the last row. I had 30 rows for this test and the macro went from row 2
down to row 29. So I added some more rows so that I had 60 in all and
re-ran the macro. This time it went down to row 59 - how do I get it to
go all the way to the bottom regardless of how many rows I have?

Lastly you said that
each if/statement will overwrite the ones above it. So if the cell
contains Criteria1 and Criteria3, the results will be Criteria3 (i.e.
"HG").

It might be that I've got my wires crossed and havent got the above in
the right order but in a cell I have LSIL-CIN I/HPV:ASC-H

Going by the criteria order above - it should look at LSIL first and
therefore its a 'LG' however Criteria 3 finds ASC-H and therefore it
overwrites 'LG' and makes it a 'HG'

So far this isnt happened. What its doing is looking in A2 and finding
LSIL-CIN I/HPV:ASC-H and putting the 'LG' in B2 and then it puts 'HG'
in C2 rather overwriting 'LG' in B2. I would prefer it to overwrite it
- is this supposed to happen or have I done something wrong?

many thanks for your time and patience.

regards

Steve


--
ghobbit
------------------------------------------------------------------------
ghobbit's Profile: http://www.excelforum.com/member.php...o&userid=12385
View this thread: http://www.excelforum.com/showthread...hreadid=541209

Hi, sorry. Those were my fault I think. This should do it though. The
reason it was going to the cell to the right was because of the "2" for
the column in the if/statement... I changed them back to "1". Also,
the error in it checking all but the last row should now be fixed as
well.

Sub Test()
Dim Screener As String
Screener = "A" 'Change this to the proper column

Dim Criteria1 As String
Dim Criteria2 As String
Dim Criteria3 As String
Dim Criteria4 As String
Dim Criteria5 As String

Criteria1 = "LSIL" 'Change as needed
Criteria2 = "ASC-US" 'Change as needed
Criteria3 = "ASC-H" 'Change as needed
Criteria4 = "HSIL" 'Change as needed
Criteria5 = "G1" 'Change as needed


'(you can add more criterias if needed)
'Note: "HS1" criteria comes AFTER "S1" not before
'Note: search is Case Sensitive

Range(Screener & "2").Select 'Assumes that you have a header in Row 1

Dim iRow As Integer
Dim iTotalRows As Integer
iRow = 0
iTotalRows = ActiveSheet.UsedRange.Rows.Count

Do
If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria1 & "*" Then
ActiveCell.Offset(iRow, 1).Value = "LG"
If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria2 & "*" Then
ActiveCell.Offset(iRow, 1).Value = "LG"
If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria3 & "*" Then
ActiveCell.Offset(iRow, 1).Value = "HG"
If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria4 & "*" Then
ActiveCell.Offset(iRow, 1).Value = "HG"
If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria5 & "*" Then
ActiveCell.Offset(iRow, 1).Value = "NEG"

'If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria6 & "*" Then
ActiveCell.Offset(iRow, 1).Value = "HG"

iRow = iRow + 1
Loop Until iRow = iTotalRows

End Sub

ghobbit Wrote:
Hi

Many thanks for your help and explainations.

I tweaked the code a little as per the instructions you included with
it and it works more or less as I had hoped it would. Except for a
couple of things - here is the code as I have it at the moment

Sub Test()
Dim Screener As String
Screener = "A" 'Change this to the proper column

Dim Criteria1 As String
Dim Criteria2 As String
Dim Criteria3 As String
Dim Criteria4 As String
Dim Criteria5 As String

Criteria1 = "LSIL" 'Change as needed
Criteria2 = "ASC-US" 'Change as needed
Criteria3 = "ASC-H" 'Change as needed
Criteria4 = "HSIL" 'Change as needed
Criteria5 = "G1" 'Change as needed


'(you can add more criterias if needed)
'Note: "HS1" criteria comes AFTER "S1" not before
'Note: search is Case Sensitive

Range(Screener & "2").Select 'Assumes that you have a header in Row 1

Dim iRow As Integer
Dim iTotalRows As Integer
iRow = 0
iTotalRows = ActiveSheet.UsedRange.Rows.Count

Do
If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria1 & "*" Then
ActiveCell.Offset(iRow, 1).Value = "LG"
If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria2 & "*" Then
ActiveCell.Offset(iRow, 1).Value = "LG"
If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria3 & "*" Then
ActiveCell.Offset(iRow, 2).Value = "HG"
If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria4 & "*" Then
ActiveCell.Offset(iRow, 1).Value = "HG"
If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria5 & "*" Then
ActiveCell.Offset(iRow, 1).Value = "NEG"

‘If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria6 & "*" Then
ActiveCell.Offset(iRow, 2).Value = "HG"

iRow = iRow + 1
Loop Until iRow = iTotalRows - 1

End Sub


I noticed where it says
Range(Screener & "2").Select 'Assumes that you have a header in Row 1

I had a header and when I ran the macro it did its job down to all but
the last row. I had 30 rows for this test and the macro went from row 2
down to row 29. So I added some more rows so that I had 60 in all and
re-ran the macro. This time it went down to row 59 - how do I get it to
go all the way to the bottom regardless of how many rows I have?

Lastly you said that
each if/statement will overwrite the ones above it. So if the cell
contains Criteria1 and Criteria3, the results will be Criteria3 (i.e.
"HG").

It might be that I've got my wires crossed and havent got the above in
the right order but in a cell I have LSIL-CIN I/HPV:ASC-H

Going by the criteria order above - it should look at LSIL first and
therefore its a 'LG' however Criteria 3 finds ASC-H and therefore it
overwrites 'LG' and makes it a 'HG'

So far this isnt happened. What its doing is looking in A2 and finding
LSIL-CIN I/HPV:ASC-H and putting the 'LG' in B2 and then it puts 'HG'
in C2 rather overwriting 'LG' in B2. I would prefer it to overwrite it
- is this supposed to happen or have I done something wrong?

many thanks for your time and patience.

regards

Steve


--
Ikaabod
------------------------------------------------------------------------
Ikaabod's Profile: http://www.excelforum.com/member.php...o&userid=33371
View this thread: http://www.excelforum.com/showthread...hreadid=541209