Find Phrase And Look Up Corresponding Cell Value
Mar 24, 2009
I have three columns.
Column 1: A list of items with a very long text description in each cell
Column 2: The Key phrase found within the long description in the cells of Column 1.
(The key phrase is different for each cell).
Column 3: Custom shortened description in text format.
Column 4: Formula to return the Custom shortened description, equivalent to the item in Column 1
How do I create a formula in Column 4 so that Excel searches for the Key phrase found within the string of Column 1 and returns the custom description in Column 4?
View 9 Replies
ADVERTISEMENT
Apr 23, 2008
I am trying to write a code for VBA code for find and replace, I want to find a particular phrase (i.e. 1. Value Added Processing) which is all in one cell and replace it a range of cells of other cells which is contained on a different sheet.
So basically the original 1 cell would be replaced for anywhere between 1 to 20cells. Depending upon what I type in.
Worksheets(1).Range("B2:B50").Replace What:="1.ValueAddedProcessing", Replacement:= _
"Sheet1!A1:A11", LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
View 9 Replies
View Related
Aug 27, 2013
I am trying to come up with a macro that will find rows that contain the words �as % of Revenue� and then calculate and apply the appropriate formula to the actual and budget columns. I have attached a sample of what the source data look like.
The formula divides the category�s total expense by the revenue in row 4. For a complete macro, I would want to also subtract the budget result from the actual result and put its result in the �better (worse)� column. I need to use this on 12 or so different workbooks which is why I�m wanting a macro.
Here is my attempt to accomplish this task; however, I quickly realized it is woefully inadequate because I neither understand how to apply it to the different columns or how to find the next instance of �as % of revenue�).
VB:
Sub Macro1()
'
' Macro1 Macro
'
Range("B10").Select
Cells.Find(What:="as % of Revenue").Activate
Range("B20").Select
ActiveCell.FormulaR1C1 = "=R[-1]C/R10C"
Range("B21").Select
End Sub
View 8 Replies
View Related
Mar 27, 2014
I have the table, as shown on left in the appended image, and the final result should be the right one.
The steps are written as 1, 2, 3 and 4.
Untitled.png
View 13 Replies
View Related
Jan 31, 2013
I have taken over this spreadsheet for my work, and it is basically a statement in excel. What I want to do is find a list of invoice numbers in column B populated from a remittance, and then replace column F to say a specific thing depending on check number and date paying for that invoice. So if a check printed today I would have it replace column f to say paid 1/31/13 check # xxxxx. Currently I am searching for each invoice indivudually and then replacing with check number and date. There are about 200 invoices per month that I deal with, and it is a big waste of time!
For example from this
invoice #1 / reconciled (DATE)
invoice #2 / reconciled (DATE)
To this:
invoice #1 / paid (DATE) check # (xxxxx)
invoice #2 / paid (DATE) check # (xxxxx)
View 2 Replies
View Related
Aug 15, 2013
Text configuration - how can I tell a cell to pick up a phrase from one cell, but if that phrase is equal to one particular phrase, then produce a blank.
View 3 Replies
View Related
Jun 4, 2009
I am very comfortable with SUMIF, but need to search a range any any cell containing words or phrase "20%" then need to sum those corresponding cells only.
I.e., something like: SUMIF(A1:A10,..."20%",C1:C10) .... only sum all C cells where an A cell contains any combination of words with "20%" in them.
View 2 Replies
View Related
Nov 4, 2006
I'm doing a macro to get the last word in a phrase
View 7 Replies
View Related
Nov 3, 2008
I need a macro that searches my spreadsheet for a keyword in Column B. If it finds the keywords (or an array of keywords would be even better), it then deletes the entire row above the keyword, the row with the keyword, and the row below the keyword.
So for example,
(Column B)
1 - Row Above
2 - Keyword Row
3 - Row Below
If the macro finds the text in column 2, it would delete 1, 2 and 3.
I need to have a second macro that performs a very similar function.
it copies the 3 rows (similar to the example below) to a different sheet based on the keyword.
View 3 Replies
View Related
Aug 3, 2009
I have a phrase in a listbox formated as such: "Company Name / Company ID # / City, State"
I need to be able to seperate the values out into seperate categories:
Company Name
Company ID #
City
State
Is there any way to do this within the code?
View 5 Replies
View Related
Jan 26, 2014
I would like to write a macro which checks if phrase from column B (lets say 13845) can be found in column A cells.
For example DDH113845G389.
I think that it should be some kind of loop but I'm afraid I can't handle it.
View 6 Replies
View Related
Nov 15, 2006
i have a list of information, and from that i want to extract a certain piece from that information and out it in a new cell...
eg. 'KLM00506', 'KLM00409-10', 'KLM00821-9' etc
as you can see, the info is not in the same length
what i need is to take out the FIRST 6 characters and have the remaining to be in a new cell
anyone has an excel formula that i can use ? not really interested in code, cause in this particular worksheet, everything is at its' place, no code required
View 4 Replies
View Related
Jun 4, 2014
In the attachment, on the totals sheet I am doing a count of the results on Sheet2. Under "Alcohol as it Applies to Me" on Totals I am trying to count the 5 different categories, but the original question is a pick all that apply so at times there are multiple answers. I can't figure out the formula to count each phrase when there is multiple answers.
HRA Results.xlsx
View 2 Replies
View Related
May 14, 2014
I have a cell with a long list of phrases all separated by commas.
So it would appear, chicken, beef, steak, soup, fish
I want to create a rule in a cell to identify if one of those phrases comes up - so if chicken comes up then a 1 would appear.
To make it interesting is it possible to grab two words from the cell, so grab the words chicken and beef from a mass of words in one cell and bring it across.
I have these cells which contain huge amount of text and preferences and I am looking to identify these entities on if they contain these words.
View 2 Replies
View Related
Mar 21, 2014
Let's say this is my data. It's already been sorted so that like entries are all grouped together.
Ex1
Ex1
Ex1
Ex2
Ex2
I need to make a macro that numbers my data like below.
1
Ex1
2
Ex1
3
Ex1
1
Ex2
2
Ex2
Essentially, my data is many columns and sorted on a bunch of different levels. I need a quick way to assign a rank to every row in the same group, to see at a glance which performed the best.
View 3 Replies
View Related
Jul 23, 2014
I try to search for only one word/phrase in range of cells. I would like formula to return the cell value that contains work/phrase I asked to look for.
For example: A1 contains "Apple", C1:3 contain "Cherry juice", "Apple pie", "Orange bonbon". I want D1 to check which cell from range C1:3 contains word/phrase from A1 and show it to me - "Apple pie".
View 3 Replies
View Related
Apr 11, 2012
I would like to search for a keyword or a phrase in a website using excel.
Generally we copy the keyword or a phrase and paste it in google and go for search. but i wanted to do this using excel as i have to search same pharse in multiple websites.
View 1 Replies
View Related
Jun 24, 2012
How I can count the number of times each unique phrase in row "A" is repeated?
For example if my data set was
Blue
Green
Black
Green
Red
Red
Red
Red Hat
how can I get excel to count the number of times and return data like
Blue 1
Green 2
Black 1
Green 2
Red 3
Red 3
Red 3
Red Hat 1
View 3 Replies
View Related
Jun 8, 2007
I have a large keyword list in a sheet called "AllKWs" In ColA from A3 downwards).
What I'd like to be able to do is this, which I'm sure will be complicated, but I will explain.
Say keyword phrase sheet has 25,000 rows of data (could be more/could be less).
I click an assigned macro button.
A pop up box appears.
I type in a word or words I'd like some info on, so for example I type in a word or words like "car rent"
It then returns for me on a new sheet called "Multi Keywords" a lot of data on this sheet, which would hopefully be as follows:
All Row 1 will contain Column headings
All row 2 will contain Total Counts (I'll explain in a minute this row)
So, all data to be returned from Row 3 downwards.
OK, as to the data to be returned.
All returned data In all Columns to show data in descending order by No of occurrances/appearances
Col A (From A 3 downwards) = The actual number of 2 word appearances (In this example that contain the words "car rent"
In Col B = All 2 word Phrases Containing ("In this example "Car Rent")
(As a note, In this example, ColA (CellA3) could only show the number"1" and ColB (B3)could only show the phrase "Car Rent") once. (As there isn't no other possible combination).
In Col C =The actual number of 3 word appearances listed in descending order That
contain the word "Car rent"
In Col D =All 3 word Phrases Containing "Car Rent"
In Col E =The actual number of 4 word appearances containing "Car Rent" listed in descending order
In Col F =All 4 word Phrases Containing "Car Rent"
In Col G =The actual number of 5 word appearances listed in descending order
Actually if anyone can crack this I really do take my hat of to them.
OK,A few more points,
Cells B2,D2,F2,H2,J2,L2,N2,P2,R2, All contain the word "Total:" and if the macro can fill in the number as appropriate.
So for example Cell L2 (For 7 word phrases) would say something like "Total:42" (If in Col L From L3 downwards the macro found 42 7 word phrases that contained the words "Car rent"
Ok, Cells A2,C2,E2,G2,I2,K2,M2,O2,Q2 All these cells will contain the word "Total". So these cells would list the combined total number of occurrances of all the phrases.
So for example cell K2 might say "Occur:324" as the total number of occurances of 7 word phrases that had the words "Car rent"in.
OK. as an example, I will post a code that Jindon wrote for me sometime ago. I'm posting this now, as it is very similar in what I would like this macro to be able to do, and might help as I'm sure this 1 will be complicated. This 1 looks for a phrase, returns by No of occurrances etc, but for all the combinations (Word lengths) within the Keyword phrase list, rather than what I'm asking for now, which splits them into Number of words columns.
Here it is anyway:
Sub NicheKeywordFinder()
Dim a, dic As Object, X, myTxt As String, b(), c(), n As Long, i As Long, e, s, myTotal As Long
myTxt = InputBox("HuaHinCarRental - Niche Keyword Finder") 'change to suit
If Len(myTxt) = 0 Then Exit Sub
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = vbTextCompare
ReDim b(1 To Rows.Count, 1 To 1): ReDim c(1 To Rows.Count, 1 To 3)
With Sheets("All KWs") 'change to suit
a = .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Value
End With
View 9 Replies
View Related
Aug 24, 2007
Each cell in Range("A1:A2000") contains a remark, each phrase or remark is
Between 5 & 70 characters all written without line feeds (carriage return,i mean Alt Enter) Just spaces between words. What I would like to do in every cell is to force a line feed (Alt Enter) every 10 characters, and if the 10th character happens to be in the middle of a word I want the line feed to be inserted at the end of this word. Note that the phrases are not necessarily multiples of 10.
View 2 Replies
View Related
Feb 18, 2013
I am trying to search for a phrase in a particular column but the excel search function is returning !Value as an error...
Both fields are text so nit sure what is wrong here.
I have a list of cities that i want to cross reference across a cloumn of notes on another sheet. So, I really want to search for the city (Sheet1 Cell A1) in Notes Sheet2 Cell A1) and I want to return it in Sheet1 Cell B1 Whether it exisits in the notes. I then want to copy this down my list of cities in Sheet1 Column a.
View 5 Replies
View Related
Feb 7, 2007
i have a spreadsheet where I need to count up how many times a particular phrase within a text string appears. The text string will be duplicated many times throughout the spreadsheet.
For example :
Miss X was at work on Saturday
Mr XX was at work on Saturday but not Tuesday
Miss Y was at work on Tuesday
So I would like to count up how many times "work on Saturday" appears in my spreadsheet, and then as a seperate query, how many times "work on Tuesday" appears.
View 9 Replies
View Related
Apr 3, 2008
what's the fastest way to add a word or phrase in front of the data already in a column of cells?
Example:
cell contains product description and I need it to show
company name product description
View 9 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
Feb 27, 2009
I have a spreadsheet with about 20,000 lines. (The number of rows fluctuate) I need a macro to remove (delete) rows that contains the phrase "Off Peak" and "Weekend"
I was setting it up in a loop but I don't know how to make the loop end after ALL the "Off Peak" and "Weekend" info is removed.
My macro just does a find then delete the row and then loop to the find again. Is there an easier way and can the "Off Peak" and "Weekend" statements be combined into one search
Do While
Cells.Find(What:=" Off Peak", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
Selection.EntireRow.Delete
Loop
View 9 Replies
View Related
May 27, 2009
I want to find the equation that will add a certain phrase in one column and put a value in another column. For example, suppose I want to find out how many people in column A has a red shirt. In the column there are a variety of colors. If they have the red shirt then I want to put a yes in column B.
Another example that I've struggled with is if I want to find out if someone had a birthday between March 1 and March 31 (a certain time) in column A, and put the value of all those people in another cell?
View 9 Replies
View Related
Apr 30, 2014
I have 10 cells in a column. I have a drop down list in each that is the same in each. If all 10 cells have the same item selected from the drop down list, I want a separate cell to list Yes or No. I've tried a few variations with no luck.
View 4 Replies
View Related
Oct 16, 2013
I have big library books database file. now i want to re check all the book with physical stock and mark in excel file.
i have one more file with books barcode no.
now i want to mark multiple cell in sheet..
View 3 Replies
View Related
May 4, 2009
If i have a range say E12:O12 and want to find the last cell before blank, lets say it finds m12 as the cell with the value before blank. then with m12 it needs to determine weather row 11 in the same column has Text either "S" or "F" if "S" then m12 = t if "F" then m12 offset(1,-1) = t. And just to make things more difficult i need the range E12:O12 to step 2 as well until it gets to E208:O208 .Noting that row 11 never changes and will always have either an "S" Or an "F"
and also t = time()
View 7 Replies
View Related
Sep 26, 2013
(I am using Excel version 14.0.7106 and MS Office Professional 2010.)
I have a macro in which I have named a range of numbers in a spreadsheet, used the "find" function to find a particular number in that range, and now would like to copy some information into a cell in the same row as the found number. However, when I try to move over to the cell that I'm copying to, it only goes to the 1st row in the range that I have defined.
Here's the portion of code I think you need to see. Everything works...it finds a match...but then I don't know what to do from there to get the information to the correct row:
'Grab the 1st project number
Range("C8").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value "" Then
prjnum = ActiveCell.Value
Selection.Offset(0, 8).Select
[Code] ..............
View 7 Replies
View Related
