I'm trying to find if the description in Column L has any of the following words: (irrigation, sprinkler, water). The cell does not need to have all of the words, but just 1 to make the IF formula return a yes. view the example below and let me know where I am making the mistake. I don't want the case to be sensitive.
This is what I have so far, but it is only finding the first word.
This example returns nothing:
Text in Column L
Main irrigation line broken Need to repair
Formula in Column S
This example returns Yes:
Text in Column L
Irrigation broken sprinklers and broken lateral li
Formula in Column S
So I have a cell with multiple words in A1. I want to check for the word "red" in A1 and if red exists return a value of "red" in B2, IF "red" is not found then search for "blue", if "blue" exists return a value of "blue" in B2, if "blue" is not found is not found then search for "green", if "green" exists then return "green" in B2, if none exist then return value of "none"
My current formula is this: =SUMPRODUCT((Str311A=1)*(Str311C=15)*(Str311G<>" DM "),Str311L). All the items starting with Str311 are formula's associate with a name. What I'm trying to do is add one more factor into the formula. I need it to look in column I for any cell that contains the words L/S. I tried modifying the formula to this but it does not work:
What formula should I use to search a certain text that might be on the beginning, middle or end of a cell to use it as a condition for an IF formula to return a related description. In the example below the text would be "deposit".
Looking for a formula (not macro) that can do the following:
I have a spreadsheet and in one column, it has last names of clients. In the next column, it has full names of clients that are the same account, but different system with different descriptions for the name. I need to lookup the column that just has last name and see if that last name exist anywhere in the "Full name" column. If it does, then I need column C to say "Yes" or something along those lines.
The "full name" column isn't always in the same format and sometimes doesn't even contain the "last name." Sometimes the last name will be first and sometimes there's a comma and sometimes there isn't a comma. The spreadsheet attached shows a good short example of what I'm working with...
I need a functionality in Excel, but I can't get it done. I want to search multiple titles on multiple words.
1. I have a list with titles (tab A) 2. I have a list with words (tab B)
Now I want to scan all titles on words that appear on tab B and return the value of the word.
Title (tab A - column A) Return (tab A - column B) Words (tab B) BrandX Shampoo Big Shampoo Eau de Toilette BrandX Nailcutter Nailcutter Nailcutter BrandX Eau de Toilette Eau de Toilette Shampoo
So for row 1 the word "shampoo" is reconised in the title and returns this value. Same for the other rows. I need the functionality like the formula below, but then with more words (from a list). I don't want to work with "OR" because the list with word is about 200+ and will be more in future.
I want to search say in column A for specific words say ďDonĒ and then write in another column (say column 2) as ďoneĒ. Then it should keep searching for specific word until the last cell in that column and keep writing specific word in another column. It should do nothing if that specific word is not found.
I was able to use IF(ISERROR) but there is limitation of max 6 searches. Also i was able to achieve it by so many iteration of IF(ISERROR) and using autofilter.
My goal is to search around 30 to 40 criteria in one shot or by writing macros. I guess i have presented my problem properly.
I am having such a difficult time creating a macro that will reduce the 5+hours I have to spend each week manually copying & pasting all of this data. I making an IMMENSE difference in this worker bee's life!
I have a workbook with two sheets (Sheet1 & Sheet2). Sheet1 has license #'s in column A and the state that the license belongs to in column B like this:
COLUMN ACOLUMN B11111Alaska11112Alabama11113Arkansas11114Arkansas Sheet2 has three columns. Column A has the license #'s, column B has the state that the license belongs to and Columns C shows a line-of-authority tied to that license #.
I'm trying to write a macro that will compare the license # and state in Sheet1 to the license # and state in Sheet2. If it matches, append the contents of Column C to the corresponding row in Sheet1.
Here's the thing...Sheet2 contains the entries for all licenses in the company (so this table is HUGE). And there are multiple entries for each state license # (notice how there's two entries above for AK license # 11111 - one for the Property line and one for the Casualty line.
After my macro is run, I want Sheet1 to show all the lines-of-authority on a single line. So if I ran my macro on the above example, after it's run I would have this in Sheet1:
COLUMN ACOLUMN BCOLUMN C11111AlaskaProperty Casualty11112AlabamaProperty Casualty11113 ArkansasLife Health11114ArkansasLife Health
Each Cell in Column A has a different long text string.
I need to see which (if any) of 10 specific small text strings exist within each long text string. Depending on which small text string is found I want to return a 3 digit code. If no small text string is found I want to return "Not Found"
E.g.: - Cell A2 contains "randomtext,randomtext,APPLE,randomntext" - I want to see if Cell A2 contains any of the words APPLE, ORANGE, CARROT. - I want to return "APP", "ORG", "CAR" or "Not Found"
Q: What is the most elegant way to accomplish this within a single formula that I could paste into each cell in Column B?
I've been working on a method to search a single column of 1000+ cells containing a paragraph with a 250+ word keyword list (also in a single column). I'm needing only complete words to be searched/ displayed regardless of spacing & punctuation. I've tried multiple methods the past few days and the one that worked (Macro with 4 complex formulas) was unusable when scaled out to all 1,000 rows due to the extremely long calculating time. I don't know VBA yet but was able to modify and create a user defined function that does everything I need except it displays string results that are not whole words (Ex. search for air, word in cell is fair, displays a result for air).
I have a key-word, and I need to search a 90,000 words in a range where i should find several words that have 2, 3 or 4 letters of the first letter of the key-word, then find several words that have 2, 3 or 4 letters of the second letter of the key-word,
How to determine the correct formula for this requirement?
Assuming I have 2 individual excel files and an index excel file (in reality, there are more than hundreds of file). For index excel, once the user enter part number (eg. 1 or 2), the excel will look for the part number excel file and determine vlookup function.
The only problem I have here is I can not make the index file automatically add the part number shown on most left column into the required formula (replace the part number file section).
I tried use the indirect function but this require each file to be opened, which is not possible for actual use. I am looking for a function that can work in closed worksheet.
I have a single column with multiple rows of data that contains multiple sports words (e.g.: football, basketball, track, hockey, swimming, skeet), and I want to find all cells that contain football, basketball and track. I also want to ignore case.
In this example only 3 rows would be found (3, 4, 8).
Is it possible to combine search results into a single cell?
On my spreadsheet I'd like to select from 4 columns and combine the result into 1 cell. I'd like to search on equipment number in column K that meets a condithion in column H, if this is a match I'd like to show the result date in column P and the duration in column R. Columns P & R shown in 1 cell...
Sort of like this... if "K" = "CV537" and "H" = "0" then show the resulting date from "N" and duration from "R" in one cell somewhere.
I have some 100k+ cells with values as "ID_code - Value_1 - Value_2 - Value_3". Lets say this is sequence A.
"ID_code" is a fixed 6 digit value. "Name_1", "Name_2" and "Name_3" are variables and they also vary in length, but they never contain a sign "-". Sign "-" is only used as separator between these four values.
Now, VBA code that would rearrange all the values in a selected column from the one as listed above to a different sequence B, for instant:
Im am trying to create a search marco button that allows me to search in multiple worksheets in one work book. I came across this CODE the first part of it works. It pops open user input box and ask for the word that i would like to search but the this error message pops up Runtime error1004 Method 'range" of object'_Global'failed and i dont know what to do
Private Sub SearchButton_Click() SearchString = InputBox("Enter Search String", "Search") If SearchString = "" Then Exit Sub For Each c In Range(myRange) If InStr(LCase(CStr(c)), LCase(SearchString)) Then
I have a workbook with many many sheets in it. The first sheet contains a single column with about 10,000 different values. I'd like to use each of these as search criteria against ALL data in the other sheets (of which there are a good 50 or so). If matches are found (they don't have to be exact case), then I'd like two things to happen:
1. The rows containing the matched search criteria in the first sheet are highlighted.
2. In the cells adjacent to the search criteria in the first sheet, hyperlinks to the matched data are created and named after the sheet upon which this matched data appears.
I've attached a sample file to this post with ideal sample 'answers' to queries made of the first 2 terms.
I have three parameters that users on my spreadsheet will be using. They will be searching using the 'Brand', 'MPAN' and 'Tariff' fields.I would like to have drop down menu's for then to select the correct search option. I then have another sheet within the same workbook with all the data in it with columns headed of Brand', 'MPAN' and 'Tariff'. It has to be an excat match using those three parameters though. I would like the parameters the user selects on the first sheet to be searched and found on the second sheet, with the figures in the cells next to the search result getting fed back into a set cells back on the first page.
I have a spreadsheet that has relatively clean data, but at the end of every row is a long notes fields (often filled with several paragraphs of text). I'm trying to search inside each one of those notes to see if it contains certain boilerplate language / legalese / key words.
If I was just looking for one word, it would be easy -- I would write =IFERROR(IF(SEARCH("keyword",E2)>0,"Yes",""),""). That way, if the keyword is present, it returns "Yes", and if it's not present, it returns a blank.
What I want to do, though, if look for a long list of keywords simultaneously, and if ANY of those keywords are present, have it return a "Yes".
So I could do something like =IFERROR(IF(or(SEARCH("keyword1",E2)>0,SEARCH("keyword2",E2)>0,SEARCH("keyword3",E2)>0,SEARCH("keyword4",E2)>0)),"Yes",""),""), but that seems horribly inefficient. Especially since my list of keywords is likely to change over time.
So what I want it to do is search each cell by simultaneous reference to an ever-changing table of keywords (call it [KeywordTable]). And I can't figure out how to do that. The search function is resisting all of my efforts to put multiple search values / a range of words inside of it.
To reiterate: the goal is to look at one cell filled with text, ask "does the text in that cell contain any of the keywords contained in [KeywordTable]", and if the answer is "Yes" return yes, and if the answer is no return no (or blanks).
I need one formula to copy and paste the checkcard data changing it from this "CHECKCARD XXXXXX5623" to "CHECKCARD 5623", but I need one formula to look for multiple known checkcard numbers. I will have more then one typically in the bank download so I need to search and copy at least two known numbers as shown below.
I am providing the formula that I am using right now, "=IF(ISNUMBER(SEARCH("5623",B3)),RIGHT(B3,41),"")" but it only works for one checkcard number? Also I don't really need to capture the DEBIT or CREDIT that will always be at the end, it would be redundant. If it's easier to write the formula including that data that is no problem. What I want to copy and move into an adjacent cell is in red.
Here is the source data: Row 2 - SONIC DRIVE IN#1531 AUSTIN TXDEBIT FOR CHECKCARD XXXXXX5623 DEBIT Row 3 - JASON'S DELI # 125 Q64 AUSTIN TXDEBIT FOR CHECKCARD XXXXXX9718 DEBIT