I'm trying to create a spreadsheet using several others in several different formats. What i need to do is search for a text string eg "EVP" in one column and return the numerical value of the cell in the column next to it - then sum all the values and create a table in the new sheet eg.
EVP sheet1!(value)+sheet2!(value)...etc...
Imagine the columns below are labelled A - D. Is there a way to search for the value 'SPLT' and return the value in column D?
Great Scot Dried Peas 3610.6366
GT SCT GRN SPLT PEAS480.151
GT SCT MRWFT PEAS1230.2158
GT SCT SPLT PEAS 1900.3157
Haven 370.10
Is there a way to have a formula search an entire row containing certian text, for example "x" and have the formula return the letter of the first column that text is found?
View 8 Replies View RelatedI have a list of product numbers in col. A. In col. O I have a list of file
names that contain the product numbers as well as additional characters. I
need a formula that will search col O for the first instance of the text in
cell A2 and return that value.
The next formula will return all instances that contain the text found in A2.
I have an array 20 Rows x 42 Columns, which contains a competition draw.
I need to search this array for a unique value and return whatever the time is in the first column on the same row as the value appears, and enter it into column C in the Womens Times sheet.
I also want to return into column D the court number from row 3.
The reason i want this automated is as teams enter / withdraw we may need to drag the games from court to court to fill gaps, so i want the Womens Times sheet to update accordingly.
I have been messing around with index and match, but cannot quite get it to return what i need.
I have attached an example ... on the sheet "Womens Times" in column A there is a list of game numbers ( #1W etc etc) indicating womens game #1 and so on. The main sheet i am using also has a seperate tab for the mens games, hence the designator of W or M on the end of the game number.
I am trying to get a formula that will search one column range “B” and pull data from another, “D”. Dates are down column “B” and some of those dates are repeated several times. In column “D” there is only one piece of data (a number) entered for each day. Eg, if 08/06/2009 has been entered 3 times in column “A”, there will only be data entered in 1 of the cells of column “D” and blank cells in the other 2.
Column B -- Column D
07/06/09 -- 54000
08/06/09
08/06/09 -- 62000
08/06/09
09/06/09 -- 61000
I couldn’t get the LOOKUP function to work properly, as there are duplicate dates in column “B” and I often got a result of 0 returned.
As I’ve only got 1 piece of data added in column “D” per day and any duplicate days would just have blank cells in “D”, I can actually get a SUMIF function to work, SUMIF(B3:B60,DATE(2009,6,8),D3:D60). Although it does work, it doesn’t feel right using it and I would prefer a formula that would return just the one cell, instead of the sum of a range of cells.
I want to search within cells A4:F25 for "B71 - Brown" and show the number that is in the cell below this text. If there is multiple cells with "B71 - Brown" I would like the cell to show the total.
So for example,If the formula was looking for "B71 - Brown" in the cell it would show 8136. (A13+A21+D25+E17). If i changed it to "Lavender" it would show 2380. (C5)
Also this data is linked from several other sheets and the values i search for will change and will need to update.
I have a spreadsheet containing several names. I want a formula or macro that will search a string of text within the cell. If the text exists within the cell, I need a code to populate. For example,
In cell E2, search for a phrase anywhere in the cell that contains "COM". In cell H2 look for the exact phrase "ASB". When "COM" is found in E2 and "ASB" is found in H2, in cell U2 return the value "COM ASBTVL".
In my case, E2 may not contain "COM", it may contain "DEN" instead. In that case, if E2 contains "COM", and if "H2" contains "SVC", then in U2 populate "COM APPSVC"
I'm guessing in my code I will need to list a set of criteria that will search row by row and return certain values when finding the text I specify.
Would a macro be best for this? Does anyone know how I would put that together?
I have text in column X and dates in column A...
How can I return the last (latest) date (column A) that "hold" was used (in column X)?
ok I have multiple columns where Col-A can have any number between 1 and 5. Col-B has a 3 digit number in it. i want to search all of column A and determine if there is a 1 in it, if so i want it to print the value of col b into a specified area.
here is an example of my form.
COLA | COLB |
--------------
1 | 324 |
--------------
2 | 325 |
--------------
1 | 327 |
---------------
this is basics, i will be using this to search col-a, determine what number is in the column, then print the number found in col b, on another page in a specific place, on the other page i have squares labeled 1-50. in a grid pattern, 10 squares per row. im trying to get all the entries in col-a, that are a 1, to put whatever value is in col-b in the first box of the grid, whatever is found in col-a with a 2, the value found n col-b of that row, into the grid box labeled 2 etc......
I have a value stored in variable A. I need to search one column for the location of that value and return the row value. there is no chance for a duplicate entry. Is a loop my only option, or is there a find command in VB?
View 9 Replies View RelatedI have a workbook with two worksheets.
On Sheet1 I have two columns 'Date' & 'Serial Number'
I want to search the 'Date' column for entries that contain todays date & then display the corresponding 'Serial Numbers' in the first column of Sheet2.
how to find text from a row in column, where is a lot of text and return row number, of located text?
View 15 Replies View RelatedI have a workbook containing multiple sheets for documents issued to manufacturers. I have a summary sheet which contains all of the documents and I would like to be able to look up a document number against all the sheets, and return the name (or names if multiple finds) of the sheets containing this document.
I have got close to solving this by finding another post[URL] .... but I believe this doesn't work because it searches on values rather than matching text.
I have attached a simplified example file with the sheets to search being Fab 1, Fab 2 and Fab 3, and the formula need to go into column B of the Main Sheet.
Example for Ozgrid Forum.xlsm
I have a spreadsheet!
I have two sheets, one of which contains film names and the other contains our tag structure for our website (which is a list of tags, their keywords and the primary parent channel they live under).
What I am trying to do is search the film titles and if any of the words match either the tag name or keywords then return the relevant channel.
Example:
Sheet 1 - Films
How To Apply Bridal Makeup
How To Fight A Donkey
How To Write Excel Formulae
Sheet 2 - Tags
Channel / Tag / Keywords
Tech - Microsoft - windows vista xp word excel
Tech - Computers - internet pc
Tech - MP3 Players - iPod Zune
So, for film 3 on Sheet 1 it would recognise the word Excel in the keyword list and return the channel Tech.
I have a formula to find if the Symbol "!" is contained in the a cell. And i want it to input "Undercut Due to Trim Edge" if there is a "!" symbol and blank if there is not. What did I do wrong?
=IF(ISNUMBER(SEARCH("~!",AL57:AV57)),"UNDERCUT DUE TO TRIM Edge","")
I have a complex list of text (a chart of accounts) with various roll ups for subtotals on one worksheet, and monthly download on another worksheet showing the name of the account and the value. I wish to use match the text to return the value in the front sheet and avoid taking the simple route of linking the cells in case additions are added during the year.
I've tried using IF and VLOOKUP formulas without success - i guess becasue I cannot sort the table in descending order?
What would be the best sort of formula to search using the text, and return a value on the same row?
Want to look in one column and find the first non-blank numerical value, then have it return a value from another column.
Used to nest multiple IF functions together from different cells, but it seems overly complicated and time consuming. Sometimes I have over 30 cells to check.
For example, if Column A contains weekly sales data by week, entered weekly, and Column E has corresponding comparison data from the previous year. I want to enter a formula to check the first row that has sales data entered and have it match up the comparison value in the other column.
Ok, imagine this data (say column A). I want to have a cell that automatically searches for the least value (will always be the first number from top) and returns it in another cell. And then repeat the same for the second number from the top.
Beg Search STA
-
-
300
-
-
1050
-
-
2200
I have a workbook that has 2 sheets. The main sheet is titled Data, and the second sheet is titled Archive.
I need a macro that will let me search all of Column A on Sheet2(Archive) and copy all rows whose Column A contains the value I input into Sheet1 B19, and paste it on Sheet1(Data) starting in cell A21. It can replace any information already on those rows on Sheet1 and I want to copy the information from Sheet2 not cut it.
This part isn't necessary but would be great, on Sheet2 in column D there is a Date it would be great to have another macro that would let me pull the same information as above but only pull a certain number of rows starting with the most recent dates.
I would like to search cells in column D for the partial string, "PIPE," (A full string may look like this: 'PIPE, 24"ODx0.375"WT API-5LX-65,ERW OR SMLS'). Then, if it's there, return the value "LF" in the corresponding cell in column C. If that string isn't found, then I'd like it to return "EA".
I know this seems pretty easy, but there's a small problem. The word "BENT PIPE," could be in Column D, in which case, I would want it to return "EA" instead of "LF".
Here is an example of the data I get each day Letter order granting Sabine Pass Liquefaction, LLC's et al 4/16/12 request to add an alternate water source etc under CP11-72.Letter order granting Cameron LNG, LLC?s 4/5/13 filing of a request to introduce natural gas or process fluids into the BOG Liquefaction Project under CP12-15.Letter order accepting NorthWestern Corporation's 8/7/12 submittal of revisions to its transmission planning process to comply with the Commission's June 8, 2012 Order under ER11-2932.Letter order approving Public Service Company of New Mexico's 12/7/12 filing of a joint Offer of Settlement with Navopache Electric Cooperative, Inc under ER11-4534 et al. How can I set up my spreadsheet and what formulas can I use to search and return a value for each text string based on the attached table (column B)?
E3a7qta.png
I need code for a search box function, that returns the information recorded in a cell for example, "Barcelona" or "London" etc), instead of the location of the cells.
I will need to narrow it down to search only the information in the following columns:
Sheet2
I2:J10932
I am totally new to VBA coding and have stumbled my way through a few things, but everything I have searched for so far has had at least one error when transposing to Excel.
I am running Excel 2007.
I have a spreadsheet that contains data for a fiscal year broken out by month and quarter. I want the formula to return the data from that month and use whatever the latest quarter is. For example in the data below:
If it has only pulled data only thru Q1 it would return Q1 for Jan-Mar, however once I have Q2 data I would want the formula to return Q2 for the months of Jan-Mar.
2013Thru Q1STOLISTOLIJan-2013STOLI BASE
2013Thru Q1STOLISTOLIFeb-2013STOLI BASE
2013Thru Q1STOLISTOLIMar-2013STOLI BASE
2013Thru Q2STOLISTOLIApr-2013STOLI BASE
2013Thru Q2STOLISTOLIMay-2013STOLI BASE
2013Thru Q2STOLISTOLIJun-2013STOLI BASE
2013Thru Q3STOLISTOLIJul-2013STOLI BASE
2013Thru Q3STOLISTOLIAug-2013STOLI BASE
2013Thru Q3STOLISTOLISep-2013STOLI BASE
2013Thru Q4STOLISTOLIOct-2013STOLI BASE
2013Thru Q4STOLISTOLINov-2013STOLI BASE
2013Thru Q4STOLISTOLIDec-2013STOLI BASE
2013Thru Q4STOLISTOLIDec-2013STOLI BASE
2014Thru Q1STOLISTOLIJan-2014STOLI BASE
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.
Basically, I need to look for an item, lets use "apple" as an example, in a range of cells, A1:D100. Then the corresponding result would be in another column in the same row.
View 10 Replies View RelatedI'm looking for a formula to search for a value in the SECOND (instead of first) column of a table array and return a value in the same row from FIRST (instead of the same or another) column in the table array. Formula would be searching for the unique production order number in the column B and return production line id from the column A.
A1 production line_id
B1 poduction_order_number
A2 L1
A3 L2
A4 L1
B2 505212
B3 504234
B4 505663
I was trying vlookup(504234;B2:A4;2;0) to make formula go search from right columns to the left but then excel is switching the search table to A2:B4 and gives #N/D!
One remark-there is no possibility to switch these 2 columns to simplify. I have to leave them as they are.
I am trying to write a formula that will return a statement if a certain month is contained in the text within another cell. Formula is =IF(ISERROR(SEARCH("Dec",Assumptions!B2)),"Ensure Journal is Non Reversing","")
Cell B2 contains a date in the format of Dec 08, so if this date contains Dec, then return "Ensure Journal is Non Reversing", if it doesn't then leave the cell blank.
At the moment it is putting in the first test for every month I select and not changing to blank.
I have hundreds of records of customer info and I am trying to categorize the job titles so that I can conduct some analysis with a pivot table. My problem is that there is about 20 different ways and variations of "manager" so Instead of autofiltering for "mrg." "Manager" "marketing manager" ect. and then replacing the text manually and using the enter and fill process (to create some standardization to compare "manager" to "director" to "c-level" to "consultant", etc.) and repeating this process over and over again...
I would like to create a VBA that would search the column "job title" for multiple text strings at once and if the text string was true in the cell then the cell would be replaced by a new text string "Manager". I was thinking a series of if functions within one vba but I am not sure if this is possible.
Search a worksheet for a user defined text string, and have excell return the contents of a predetermined column in the same row in which the text string was found.
A prepopulated worksheet has the text "gold" entered in cell T278.
1. user searches for "yellow_metal"
2. Excell finds "yellow_metal" in row 278, say in cell A278.
3. Excell then goes to predetermined column (programed as part of macro or VB), say "T", and returns the text contents of the cell in that column, T278 in this example.
4. Excell returns "gold"