Find Keywords Within Cell Text Of Another Column?

Nov 8, 2013

I'm evaluating the effectiveness of a keyword list we use to identify certain, high priorit,y medical situations from a string of text.

So, I have a column on a sheet of data (EIS Report!"B:B") in this case, and a seperate column on another sheet with keywords (Keywords!"A2:A47").

I use the formula ={OR(NOT(ISERROR(FIND(keywords!$A$2:$A$47,$B2))))} to identify if any of the keywords exist in the string.
This works well but I would like to improve my work in two ways:

1 - Identfiy, in another column on the EIS report sheet, which keyword it is that's been found within the string,
2 - On the keyword sheet, add another column of words, which if found within the string, would act as an exclusion.For example, I might use "STAB" as a keyword to look for "Male stabbed" etc, however might want to exclude "STABBING" to stop the solution triggering on "STABBING PAINS IN ABDO"

How might i adjust my formula?

View 9 Replies


ADVERTISEMENT

Find Out The Most Common Keywords In Column

Oct 12, 2009

In column A I have 50,000 cells, each containing 1 to 10 keywords. For example
A1 = "jobs"
A2 = "jobs in milton keynes"
A3 = "it jobs in milton keynes"
A4 = "sales jobs in milton keynes"
A5 = "well paying brickie work in spain"
etc
etc

At first I was trying to find out the most common keywords in column A, and I used the following code to do so

View 7 Replies View Related

Identify Specific Keywords (plural) In A Column Of Text

Nov 3, 2009

So i'm try to automate a process that I currently do using filters.

Here is the problem:

I have a list of text in a column (usually 700 or so entries).

I need to remove any entries that contain 1 of 42 keywords.

So I need a fast way to search each cell for each one of the 42 key words and flag the cell for deletion if contains anyone of the 42.

example
A
1 This field has keyword1
2 This field has keyword2
3 This field doesn't have any of the 42 keywords

somewhere else I would have a list of keywords in a column.

View 9 Replies View Related

Changing Cell Text Color WITHOUT Using Conditional Formatting Based On Finding Keywords

May 29, 2012

I have a spreadsheet that uses VB macros to calculate sums of cells based on the font color of the numbers inside. It used to be fairly easy going through each cell and "classifying" them by color, so that my macros can go ahead and sum the numbers in each respective color's cell... but now I have a huge amount of numbers and would like to automate the process somewhat. Here's an example using the A and B columns:

flight $400
hotel $150
hotel $130
meal $20
meal $15
flight $350

I tried using conditional formatting to automatically change the color of the adjacent cells based on the presence of a keyword such as "flight" or "hotel", but this change is only cosmetic, and doesn't actually change the font color (it is still the default black, hence why my color-summing macros won't work!).

I'm including a sample macro for what I use to color-sum my cells, but what I am looking to automate the color-coding process based on looking for keywords as explained above in my example. Here is one of the working color-summing macros (for red, in this case) if you'd like to use it as a reference:

Function SumRed(SelectedCells As Range)
' Adds the values of the cells where the font colour is red(3).
Dim Cell As Object
Dim x As Double

[Code] ...........

View 9 Replies View Related

Find Duplicate Keywords, And List Maximum

Feb 13, 2010

I have rows of 10 keywords. I want the macros to compare the first row against all the rows except itself, then compare the second row against all the rows except itself, the third row against all the rows except itself, basically looking for duplicates...It will then record in a column for each respective row, the max # of duplicates found with another row..

For instance lets say I was looking at the 15th row, and it looked for duplicates elsewhere. It only found duplicates in the 4th row and the 5th row. In the fourth row, there were 5 keywords common, and in the 5th row there were 7 keywords common. Therefore the max # of common words is 7 for the 15th row, and that is what is recorded.

In addition the number of rows will probably vary it could range from 1 to 500. I included an excel spreadsheet that has visually speaking how this could work, maybe using COUNTIF. Basically it counts the # of common keywords for one row with all the other rows, pastes the count #s to the right, then finds the max of those count #s, copies the max into another column, then clears the count #s, and looks up the next row, repeats the same process. It is easier probably if you look at the attached spreadsheet to get an idea of what i am talking about..

View 9 Replies View Related

Find Last Number In Row / Add 1 To Current Cell (if Another Cell In That Column Has Text)

May 23, 2014

file storage

1. Look at the "Days so far" section

2. Ignore cell C1

3. All the other cells in that row are shaded blue. Look at these.

4. Look at the "Volunteer" row - all the cells in that row are shaded blue. Look at these.

5. Wherever a name - any name - appears in the "Volunteer" row, 1 is added to the previous number in the "Days so far" section and the result is displayed in cell from the "Days so far" row above that Volunteer's name.

6. For example, we begin in C1 with a count of 12 days so far - this was manually entered

7. In cell H5, we see Henry has volunteered 1 day. The total no. of days so far should now be 12+1. Therefore, the number in H1 should be 13.

8. In cell G47, we see Joseph has volunteered 1 day. The total number of days volunteered BEFORE Joseph volunteered is 15 (see cell D43). But now, with Joseph volunteering 1 day, the total no. of days so far should now be 15+1. Therefore, the number in G43 should be 16.

9. what formula l must put in the "Days so far" row (excluding cell C1, which is manually input) to give me the "should be" results predicted in that row? I'm guessing it will be a formula which looks at each row fragment of the "Days so far" row, row by row, right up to the previous cell in that row, all within one formula.

P.S. I just want to leave the "Days so far" row blank, for any columns where there are no volunteers in the "Volunteer" row, so please don't give a formula which inserts zero for days with no volunteer, and then sums the cumulative total.

View 5 Replies View Related

Find Cell Value In Column & Return Text In Adjacent Cell

Jun 10, 2008

I have a drop down list in a merged cell B12-F12 and B13-F13 and B14-F14 . . . B30-F30.

I need the adjacent merged cell to populate a reason (text) based on the text answer in the drop down list or the entered text in the first merged cell. For example in the cell B12-F12 the user picks from the list or types in "Amiodarone." I want the adjacent merged cell G12-J12to automatically fill with "Heart Rhythm." I also want to be able to set up multiple if - them statements like if Amiodarone is entered then fill adjacent cell with Heart Rhythm and if Toprol XL then fill adjacent cell with Heart / Blood pressure and if simvastatin then fill adjacent cell with Cholesterol, etc. I have about 30 different options for cell 1 that I want to have auto fill in cell 2 based on the contents of cell 1. I've attached my file.

I want the user to be able to choose from the list or type the drug name in.

View 8 Replies View Related

Find Certain Text In Column And Then Replace All Similarities From Another Cell

Nov 6, 2013

I'm trying to find a function, or conditional format, that will allow to find certain text in a column and then replace all the similarities from another cell.

So basically, I have 3 Columns.

Redundant Text
Replacement Text
What The Text Currently Reads

Red Hats
Save On Red Hats Online
Black & Red Hats

Blue Jeans
Find Blue Men's Jeans
Blue Jeans On Sale

1) There is text that is recurring in the the thousands of the cells of Column 3.
2) I have used a program that found the recurring data, and put them in Column 1
3) Column 2 is what I want ever cell in Column 3 to change to when it matches the cell in Column 1
4) Is there a function that will find text in the 3rd Column that matches text in the 1st column (multiple cells in the 3rd column will match one cell in the 1st column)
5) Then Replace the text in the 3rd Column with the corresponding text in the 2nd column

View 2 Replies View Related

Find Text/Value & Use Found Cell, Row, Column In Macro Code

May 10, 2008

I am trying to clean up and simplify my macros by compartmentalizing frequently repeated commands. I like to search for text in column headings and define the cell containing it as a range that I can later use .column or .row commands on, so my macros are a bit more flexible. The code I currently use for this is

Dim XColumn As Range ‘the cell containing the text “X”
Cells.Select
Selection.Find(What:="X", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Set XColumn = ActiveCell

I would like to be able to create a Macro (possibly name FindSetCell()) that will accept arguments and achieve the results of the above code in other macros
I think the command might look something like FindSetCell(“text to be searched for”,”name of newly Defined Range”) But I have not figured out how to code it

View 2 Replies View Related

Highlight Keywords In Text Strings

Oct 24, 2007

I perform a =Find(word,range) for certain keywords in text strings. Is there an easy way to have excel highlight the words within the string so i can easily identify its location? The text string might contain more than one keyword.

View 9 Replies View Related

Find A PORTION Of Text In A D Column And Automatically Adds The Price To The Right Of It Together In One Cell

Mar 22, 2009

I am trying to organize it in a way that I can have all my purchases added based on my text criteria of certain purchase... Ill try to be as clear as possible. I have already organized it in a way that when I paste new months statement in my Excel file it sorts it by date, newest on the top, and I made Conditional Formatting by color on column D which states the Description of purchase. Starbucks appears in description cell but it is followed by number and location making each Starbucks cell in column D different in name. So its hard to use Exact phrase formula to combine the amount for that purchase.

My question is: Is there a formula that find a PORTION of text in a D column and automatically adds the price to the right of it together in one cell. There is a formula that looks for the whole text in the cell but that doesn't work when my name has always some extra numbers at the end of the word Starbucks

View 2 Replies View Related

Searching A Column For Keywords?

Jul 10, 2014

I am trying to NEXT my way through a column of comments and highlight the cells containing the key words. Below is what I have put together, but I know it is NOT working correctly....

[Code] .......

View 8 Replies View Related

Categorizing Items Based On Certain Keywords In Text?

Jun 9, 2011

I am looking to label an excel list of engineering drawings with 3 separate categories.

The categories are decided by key words in any of three columns:

-Drawing
-Description
-Drawing Title

I have attached a file that explains in detail and gives a mock sample of drawings.

View 3 Replies View Related

Range Between Two Keywords In Specific Column

Jan 11, 2014

Currently running a macro which selects all the rows between two specific words and pastes the selection into another worksheet. However, it runs this search on the entire workbook, whereas I'd just like it to run the search in a particular column (column D in this instance).

This is the code as it currently stands:

VB:
With ActiveSheet
.Range(.Cells.Find("financial assets"), .Cells.Find("liabilities")).EntireRow.Select
End With

.Selection.Copy
Sheets("Paste Currency").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Is there any way to focus the search on only one column?

View 4 Replies View Related

Finding List Of Keywords In Column?

Mar 17, 2014

I need to find 100 or so keywords in a column, I have the list but how would I search multiple keywords as there are 10k columns and it would take forver to search for them manually?

View 4 Replies View Related

Replace Values Between Two Keywords In A Column?

Jan 31, 2014

I have data in which there are important keywords within a row (e.g.,# Message: FacesSc0_1) that are 'boundary markers' for data that is relevant to me. What I want to do is to select the data in the column between two such keywords, replace them all with the same specific value, and delete the other rows.

For example a column may look like this:

534.88
534.78
# Message: FacesSc0_1
534.78
534.55
534.59
# Message: FacesSc0_2
534.55
534.55
# Message: FacesSc0_3
534.78
534.55
534.59
# Message: FacesSc0_4

I would like to replace the values in the cells between "# Message: FacesSc0_1" and "# Message: FacesSc0_1" with the value 1, delete the values between "# Message: FacesSc0_2" and "# Message: FacesSc0_3", and replace the values in the cells between "# Message: FacesSc0_3" and "# Message: FacesSc0_4" with value 2. The resulting column would be reduced to this:

# Message: FacesSc0_1
1
1
1
# Message: FacesSc0_2
# Message: FacesSc0_3
2
2
2
# Message: FacesSc0_4

View 4 Replies View Related

Delete Rows Between 2 Keywords In Column

Jun 26, 2008

I want to delete entire rows between two keywords.

Example:

Row 1 Connecting
Row 2 aaa
Row 3 bbb
Row 4 Unavailable

I want to delete rows 1 through to 3 and loop it until the worksheet is completed.

View 9 Replies View Related

Count Number Of Keywords (range) Found In Text String

Aug 23, 2013

I am trying to count the number of specific words contained in a specific cell. If my data were static, and the list of keywords was short, there are many solutions such as using multiple instances of the length function as proposed by @shg here.

Suppose I have the following sheet: (column C is what I am trying to achieve)

A
B
C
1

[Code]....

That is, C2 will tell that B2 contained 3 of the keywords (1X beautiful, 2X happy) and so on.

View 9 Replies View Related

How To Categorize List / Column Into Separate Ones By Finding Keywords

Jul 28, 2014

Say I have a list:

VB:
Something something apple
Something something orange
Something something banana
Something apple something
Something banana something
Apples woo
Apples And bananas
Something orange something
Something something apple pie
Something something vegetables

And I want to separate the list into separate columns/lists with certain keywords (e.g. apple, orange, banana) like so:

Apple
Something something apple
Something apple something
Apples woo
Something something apple pie

[Code] .....

The "multiple words found" part isn't a big deal (I'm not sure how I'd deal with it anyways). The "Unsorted" part is there assuming creating the separate lists won't remove the entries from the original list (which would then leave all the unsorted text).

View 3 Replies View Related

Find Text Within Cells & Place Same Text In Adjacent Column

Apr 10, 2008

I want to search for a word in column A and when I find it I want to copy it to column B.
Column A is a description that can be 6 or 7 words long. Column B is a single word.

Example:

Col A
Engine Kit, V-8, 306, forged.
I need to copy the word Kit to column B.

View 9 Replies View Related

How To Determine If Cell Contains Keywords

Nov 9, 2012

I have a column (Column B) that contains some free text and I have another column (column K, rows 3 to 12) that contains a series of keywords.

What I would like to be able to do is if column B contains one of the keywords in column K then that keyword is placed in column C.

There will only ever be one keyword per row.

View 2 Replies View Related

Pull Keywords From Cell Range And Combine In Single Cell

Aug 1, 2014

I am in need of a way to pull keywords listed in B2:B10 from text in A2:A10 and then those pulled keywords combined in cell C11.See attached document. So I have colors for keywords in column B and some random text in the column A including the keywords in parenthesis. Is there a way for a formula to find all keywords in A2:A10 and then list them in cell C11 with commas in between.So the result would be one cell C11 showing BLUE,GREEN,BLACK,ORANGE, etc.I have been trying to mess around with index and match and while I could get one to pull up I don't know how to combine it.

Keyword Range.xlsx‎

View 5 Replies View Related

Trying To Find Max Date In 1 Column For Selected Text In Another Column

Apr 10, 2009

I have 2 columns: Locations (Column A) & Dates (Column B).

I want to find the most recent date for a selected Location (stored in $G$1).

currently I have in C2: =If($A2=$G$1,$B2,"") copied down the column to the end of data (currently C153). Then in C1: =Max(C$2:C$153).

I also have in D2: =If($C2<$C$1,$C2,"") copied down to D153, and in D1: =Max(D$2:D$153) to find the 2nd most recent date.

Is there some way to condense this into just 2 cells?

See attached file for example (note the columns are currently sorted by date, but that is not always the case).

View 14 Replies View Related

Formula That Will Look For Keywords And Display Cell Content?

Sep 23, 2011

is it possible to have a formula that will look for keywords and display the cell content?

View 2 Replies View Related

IF.. Contains...then: Search Through The Text String In That Cell And Find A Certain Word, Find And Retrn A Value

May 23, 2006

I am having trouble getting my IF statement to test if the cell contains the text "sale" return "X" if not "Y". I need it to search through the text string in that cell and find a certain word, and if it finds that word, retrn a value. I am really having difficulty with is what symbol or function do I use for the logical test? (i.e. =, <>, MATCH, INDEX?)

View 4 Replies View Related

Macro To Find Last Row In Column B And Text To Next Column

Sep 3, 2013

I have a macro that copies data from one sheet to another. I would like getting the macro to figure last row used in column B from B3 on and put the word Sum below it. For example, if last row used is B64, on B65 would like the word sum on it.

I cannot just input in as the number of rows could change when macro is run. Data is dynamic.

Here is simple code I have:

Sub Weight()
Sheets("Final Month").Select
Range("A2:B1055").Select
Selection.Copy
Sheets("Final Weight").Select

[Code] ...........

View 2 Replies View Related

Reading A Column To Find More Than One Text

May 10, 2007

I have a spreadsheet and need column S to look in column J to find "AO" And "AI" once it finds this I need it to look at column P and post that data into column S. I have tried all sorts of IF's but I cannot seem to get it to work.

View 9 Replies View Related

Find If X Text String In Column

Aug 29, 2007

How do I search a given text "GNMA" from a cell (A1) and (A2) and return it in other cell (B1) and (B2).

Example:
A

1 GNMA-4565X18-ROE
2 895GNMA-12

View 9 Replies View Related

Keyword Search - Macros To Change Font Color For Specific Keywords (not Entire Cell)

May 5, 2014

I need to conduct keyword searches in excel and color the keywords a different color without changing the font color of the entire cell. now, i found some code from another thread that does this, but i cant seem to get it to work with more than 2 keywords. i need fine-tuning the provided code to do what i need it to do.

[URL]

the VBA code i used was this:

[Code].....

I was playing around with the example, the Cat_Mouse.xlsm, and when i modified the 'myList' and 'myColor' arrays, it does not work.

Cat_Mouse.xlsm

For example, I modified the code to add the word bat:

[Code] .....

When i run the macros, the word "Bat" does not become colored red. interestingly enough, when i substitute the word "bat" for the word "hat" in the "myLIST array, the word "hat" does change to the red font.

I am looking to use this code to address keywords in my excel file by coloring all key words red and i have more than 10 key words.

View 9 Replies View Related

Find Specified Text In Column And Sum Values Beneath It?

Jun 4, 2013

I have some xls files. I need to find the sum of a specific column. the column heading is always TOTAL but the column it resides in changes column to column. so one file may have the text total in column H and others will have the text total in column X. how can i make a macro so it finds the text 'total' and then sums up all the values in that column underneath it and displays it at the very bottom of that column?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved