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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
