Finding Text In Another Range?
Jun 5, 2012
I have the code below which does work and looks up a piece of text in A1 (in this case the word exhaust) within the contents of column B. If the text is found (either part or whole) it returns a Y and if not an N in column C.
It works fine but I can't work out how to get it to repeat should I then want to look at a value in A2, A3 etc without removing any Y had Y already been displayed due to a prior word in the list. Below are the results I'd want.
Column AColumn BColumn CExhaustWheel inc TyreNClockClutchNPipeExhaust - SingleYExhaust - DoubleYWindowNFront ClockYDoorNSeatNBonnetNRear PipeY
Sub FindSomeText()
'Application.ScreenUpdating = False
Range("C1", Range("C65536").End(xlUp)).Clear
keyword = Range("a1").Value
Range("b1").Select
Do Until IsEmpty(ActiveCell)
If InStr(1, ActiveCell.Value, keyword) = 0 Then
[code]....
View 3 Replies
ADVERTISEMENT
Nov 19, 2013
I am looking to set up an excel doc with 2 sheets.
In sheet 1, I am would like to be able to enter a series of words of interest/criteria to be met (in column D).
In sheet 2, I would like to paste in the text from an individual's CV. The text would be entered en masse, meaning that some cells would be empty and others would have whole sentences/paragraphs of text.
I would like a formula to enter in column A of sheet 1 which would search all of the cells in sheet 2 for the word/words in column D of sheet 1 and return either a positive or negative value.
I had tried the following:
=(COUNTIF(Sheet2!1:1048576,Sheet1!D2)>0)
This formula, however, is not capable of extracting the desired text from a string/sentence, and so only returns a positive result if the desired text is in a cell by itself.
View 3 Replies
View Related
Mar 23, 2012
I have a large file where I want to name about 30 ranges. Initially I had done this using the range addresses and it worked well, but I continue to modify the spreadsheet and add and delete rows, so I need to go back into the code and change the ranges manually. I know there has to be a better way.
What I wanted to try doing was finding some specific text. That text (in all the ranges) is actually one column to the left and 33 rows below where I want to start the named range. Additionally the named range will be a 10 x 33 range. For example, if the data starts in B1, the data to be named will be in B1:K33 and the search title will actually be in A34. Hopefully that's clear...
My original code looked like this:
Code:
ActiveWorkbook.Names.Add Name:="[MyRangeName]", RefersTo:=ActiveSheet.Range("B2:K33")
I was attempting to us the Find function with this code, but I can't seem to figure out how to tell it to look for the text (that would be in A34) and then name the range that would be offset by (-33,1) and then name the entire range.
View 3 Replies
View Related
Jun 29, 2014
I need to create a macro to do the following:
Search the activecell for a text string (a), and then either paste in text string (b) at the end of the cell if (a) is found, or text string (c) if (a) is not found.
For example, if the activecell has "AA/" in it, I want the cell to become "AA/01" (pasting in "01" at the end), and if the cell has just "AA" in it, I want it to still become "AA/01" (pasting "/01" at the end). The macro will be linked to a commandbutton.
View 7 Replies
View Related
May 12, 2014
I have a report that in column BX has a large string of text (html information). Within this text there is a phrase called "| Hear = Education Website |" The phrase Education Website can be a variety of things though (Up to 9 choices). What is a formula I could use to just pull out the text after "Hear =" but before |""?
View 8 Replies
View Related
Aug 5, 2014
I have attached xl sheet for ref.
I have value in E2(5),F2(10)=G2(5[sub of F2-E2])
I need to find E2(5) value in B2:B21 and the same of F2 also
After finding that
(eg): E2 value 5 present in B6 cell and it should come to c6 cell same of F2 also
Now It should select C6 to C11 cells.(ref of B6 to B11,ie,5 to 10)
I tried in vba and formuals but i couldnt find solution.
View 6 Replies
View Related
Sep 7, 2009
I am trying to extract text from one cell into another, for example if in cell A2 I have text " {Operating System Issue} ~Word~ I got a new PC i used to have avaya…" in cell b2 i want {Operating System Issue} and in Cell c2 I want ~Word~.
This is what i am doing, in Cell B2 in putting formulla as =MID($A2,FIND("{",$A2,1),FIND("}",$A2,1)) and in cell C2 i am putting formulla as =MID($A2,FIND("~",$A2,1),FIND("~",$A2,1)). However in cell C2 thsi si what I am getting
~Word~ I got a new PC i used
i dont want anything after "~" sign. What i need is only the text that is in betwwen "~" sign.
View 11 Replies
View Related
Oct 24, 2008
On my spreadsheet I have a column entitled: "Item Title", and the data within this column can look like the following;
View 3 Replies
View Related
Mar 7, 2012
I need to find from the second word until just before a numeric number.
Thus the text in Column H is as follow:
COLUMN H
1234234 My name is John 12 Jul
34243 I am 10 years old 23 Feb
4345345 Peter 23 Jan
So, I need the text in RED extracted only. All words are seperated by spaces. It will always start from the second word and continues until it finds the first number. All words between these must be extracted.
My formula below is to extract the 1st Word, thus all I need is the formula for the above situation and replace the one below with it.
a.Formula = "=Left(h2,find("" "",h2)-1)"
View 9 Replies
View Related
Jan 23, 2014
I've got 2 sheets. Sheet 1 is where I am inputting all the products I'm producing and giving them serial numbers, if I produce the same part twice the name of the part is the same but the serial number changes. It looks something like this...
Sheet 1:
A
B
C
1
Part Produced
Serial Number
[code]....
While Sheet 1 is constantly been filled with the parts being made with their individual serial numbers. Sheet 2 is constantly being filled with the incoming orders, But only the A Column of Sheet 2 is being changed... as an order comes in I simply type into sheet 2 what product the customer is looking for.
The problem I am having is that as I write in that another "handle" for example as been ordered into sheet 2, my normal MATCH forumla is bringing up the same Serial Number "1001" over and over again. Really I'm wanting the "B" column to update to the next available serial number, so for example "1002"
Sheet 2... as it's coming out right now (wrong)...
A
B
1
Part Ordered By Customer
Serial Number
2
Handle
1001
[code]....
The formula will need to go into Sheet 2 'B' Column, and I'd like it to come out looking like this...
A
B
1
Part Ordered By Customer
Serial Number
2
Handle
1001
[code]....
View 8 Replies
View Related
Nov 15, 2006
Column A has prepopulated list of values of various lengths, no spaces. Need to search column A for certain values at the END of each text string, and then populate the cell right next to it with another constant value.
Example: macro searches column A for cells with "123" at the END of the cell value (whole cell value could be A123 or ABC123, etc...). When macro finds the cells that meet this criteria, it places the constant value "XYZ" in column B right next to the cell in col A.
I need several variations on this theme and the # of characters being searched for at the end of each text string can vary. The constant value to be inserted in Col B can also vary. I imagine 'For Each' loops for every variation would work, I just do not know how to write the code for searching the "end of the text string" part of it
View 9 Replies
View Related
Jul 25, 2012
Book1.xlsx
I have two sheets of data and two tables (1=finial 2=data comparison) I am trying to figure out some kind of formula or vba that can easily tell me that the location point in table one data comparison matches the location in table two. The VPMDFS (location footage) in table two or NDE data can be a range, say vpmdfs.NDE + or - 3 ft. so the ILI or source record should fall in between this range. Once the match has been made I need to copy and paste that record onto sheet one all in the same row. I have been scratching my head and researching for weeks and cannot come up with a simple solution. My problem with all the scenarios that I've tried is the formulas what the data in the data comparison sheet to already be in the same row. Please reference my attached work book for examples.
View 9 Replies
View Related
Apr 4, 2009
I want to be able to find all the 0 differences (i.e debit = credit) cut that group out of that sheet and paste it to a new sheet in the same workbook. I have the code to find the 0 difference but I don't know how to select the range that makes up that difference and cut and paste it to a new page.
View 2 Replies
View Related
Jun 16, 2009
I name a range "MyList".
In another cell, I use Data.Validation.List and use "MyList" as the source.
Now I find another range named "HisList". Is there some way to determine if "HisList" is being used as a source for some other validation somewhere in the workbook?
"Trace Dependents" doesn't work.
View 13 Replies
View Related
Sep 12, 2005
If i have a range, lets name it "Names" and i want to find if the name
George is in that range, what formula should I use?
View 9 Replies
View Related
Apr 9, 2014
The data I need is in the middle of the worksheet (A16:B41), is there way to define the range so that excel only looks at that range and finds the last row of data?
Code:
Worksheets(i).Activate
lastRow = ActiveSheet.Columns("A").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).row
Range("A16:B16" & lastRow).Select
Selection.Copy
Sheets("difference Check").Activate
[code]....
View 9 Replies
View Related
Jun 21, 2006
Is there a property to, given a certain range, return the name of the range?
I have used the name property and gotten a name in the form worksheet!address, but I am trying to return the name that I have given the range.
I was planning on using:
'returns the name if it exists, else returns ""
Function findName(myRange As Range) As String
Dim name As name
For Each name In ActiveWorkbook.names
If name = myRange.Address Then
findName = name.NameLocal
Exit Function
End If
Next
findName = ""
End Function
However, this code isn't working because when comparing name to myRange.Address, name includes the worksheet that the range is on, but myRange.Address doesn't.
Even if I get the above code to work, it will take quite a while because I have many names in my worksheet, and would be running the findNames function many times
View 6 Replies
View Related
May 10, 2009
Is it possible to find text listed in a cell, that has sereral options seperated by commas?
Example:
red, green, blue, orange, purple, black (all listed in one cell)
black is or is not found in targeted cell?
View 6 Replies
View Related
Aug 7, 2009
Possible to find the position of the 1st number value inbetween text??
example : GBB_5600_OPP_WNN
View 9 Replies
View Related
May 11, 2010
In a data report i have a column which contains a mix of text and may contain an 8 digit number which could start in 0.
for example
in column A i could have "Hello how are you 01237232 I am fine"
I wish to extract the number into a seperate column, and would ordinarilly use a mid or left/ right function, however the text infront and behind the number will vary in length, which means i cannot do this.
The number will always be 8 digits, could start in 0 but will not always, and it may not be present in all the cells in this column.
How I an achieve this?
View 11 Replies
View Related
Jul 3, 2014
I am trying to create a cell that show the value of all specified rows with specific name.
For example:
Apples $2 $5 $10
Oranges $5 $10 $50
Apples $5 $ 6 $ 60
(each of these will be in their own cells, 3 rows and 4 columns)
How do I create a cell that will sum the total value of all the Apples by using Apples as the variable?
View 1 Replies
View Related
Feb 11, 2013
I have a column with text + string (e.g., T1000, T1001, etc.)
I have another column that's the same, but sometimes this column has new codes.
Right now I have to "eye" it side by side to see if there are any new codes, but I'm sure there's a better way to do it. I'm thinking of asking Excel to compare the columns and if they aren't equal, then it would be necessary for me to eye it. If they are equal, then I dont have to bother because there are no new codes.
View 4 Replies
View Related
Sep 26, 2007
I've searched around the web for AverageIf solutions and found the CSE formulas. However I've come across a big problem I can't resolve.
I have two columns of data. Column A has _similar_ text and column B has data.
The problem is the following: AverageIf does not support finding similar text. This means that a formula like:
=AVERAGE(IF(A2:A400="Info/German*";F2:F200;FALSE)) does not work.
Column A contains words like Info/German, Info/German2, Info/Italian etc. But I only need to find the average of cells that begin with "Info/German".
View 9 Replies
View Related
Oct 28, 2007
I want to find a number within a text string, then count the letters in the same string, then add the number to the number of letters.
For example, if I have 9RR in a cell, I want to add 9 + 2 = 11.
The data will always be arranged with the number on the left side of the string.
So, if the number is a single digit, I know I can use left(A1) + len(A1) -1 to get my desired result. But, the trouble I am having is what if there is a double digit number like 10RR...I'm not quite sure how to create a formula (without using VBA) to give me a result of 12 for this cell.
View 9 Replies
View Related
Apr 10, 2008
I have a mass of text in a cell. I would ideally like to search the text to format in a different font colour so it can be easily seen where the specific text is.
My current way is to use SEARCH in conditional formatting but this changes the whole cell not the specific text I am interested in.
EG. - I interested in the text PETER in my cell so would like PETER to be in red font.
A peck of pickled peppers Peter Piper picked; If Peter Piper picked a peck of pickled peppers, Where's the peck of pickled peppers Peter Piper picked?
View 10 Replies
View Related
May 21, 2008
i need help with 2 formulas to return text within a longer string.
the first result i need is a style #. this will always start at the 5th character, but the length is variable. it will always end right before the last "/" or "" in the string (there may or may not be a / or within the style number)
the 2nd result i need is a description of the style. this will always be after the last "/" or "" in the string and end before a ".", but there may not always be a period in the string.
Text string: ABC123123BASIC TEE.black
Style # result: 123123
Description result: BASIC TEE
Text string: DEF/4567LEAH/FLORAL TANK DRESS
Style # result: 4567LEAH
Description result: FLORAL TANK DRESS
View 10 Replies
View Related
Jun 26, 2014
I have two columns (one is mean and the other is max) and the third column, I have the corresponding letter. I have in total 5 rows with mins and maxes and corresponding letters. I want a function that when i put for example #3 on cell D9, then my cell E9 will spit out the corresponding letter.
See the attachment : find the corresponding letter.xlsx‎
View 6 Replies
View Related
Jul 9, 2014
=IF(COUNTIF('Deal Calculations'!E4:E30,"Pandy Mandy"),'Deal Calculations'!B4,"£0")
Above is one of the formulaes that I am using and I tried editing, to no avail.
Anyway here it is, I need to search E4 - E30 for a particular name, lets say Pandy. Now if pandy is found, then a cell on another tab has to equal a cell which is 2 cells on the left. from which the cell which contains the name is located.
here is a psuedo version of it...
Search E4-E30 Find a cell which contains 'Pandy' If pandy is found, 2 cells to the left then a cell on another tab is equal to that.
View 8 Replies
View Related
Dec 30, 2008
So I have multiple columns of numerical data,
I have to find the sum of all numbers between 10 and 40 within a column, NOT cell 10 to 40 but the actual number 10 and 40.
How do I do that?
Is it SUMIF? I cannt seem to grasp this.
View 11 Replies
View Related
Jun 6, 2014
I currently am working with a worksheet that has over 250,000 data points. It is a spreadsheet for a company in which different work order are organized. A given work order can have several parts (ranging anywhere from one component to 40ish) and thus the height of the cells in which each specific work order is placed differs from work order to work order. For each component, the spreadsheet tells you how many can currently be made based on the materials available. What I would like to do, for each specific work order, is go through the data and simply tell whether or not the work order can be completed (the quantity of each specific component needed is also given in the spreadsheet) and then report out exactly how many components can be made.
Ultimately, this means that a "limiting factor" analysis must be done. For instance, if the work order is asking to create plastic bottles that consist of the bottle, the cap, and the label and it asks for 3 bottles but only 2 caps are able to be made, the limiting factor would be the caps and thus only two bottles could be made and the work order could not be fully completed.
The spreadsheet is set up based on another worker here, I am just trying to come up with a way to analyze it. Cells A1:Axxxxx are the work order numbers and the size of these cells differ because they are merged to fit the number of components they have. The componenets available are then ranged C1:Cxxxxx etc and the quantity needed are also listed in a similar fashion. What sort of structure could I use.
View 7 Replies
View Related
