Complex List - Search For Text And Return Value In Table
Jan 20, 2014
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?
View 1 Replies
ADVERTISEMENT
Sep 24, 2009
0000AAAAABBBBBCCCCCDDDDD
1111SugarRaisinDirt
2222MilkWaterWaterFlour
3333FlourSugarDirt
4444EggsWaterFlourSugar
5555WaterMilkDirt
I looked for a way to represent my data in a cleaner way than this but couldn't find any tools....a point in the right direction would be helpful. I guess the old plug-in that converted Excel->HTML isn't available?
Column A is a finished product and B-D are the ingredients to make it however values in Column A are also ingredients (sometimes multiple times). I want a formula that searches for values in Column A within the table B1:D5 and returns an array, if possible, of the finished products where Column A was used.
For example, searching for Sugar would return "Flour, Water". Searching for Water would return "Milk, Eggs" (I'd rather not have Milk listed twice but beggars can't be choosers).
View 9 Replies
View Related
Nov 10, 2009
I have been working on a variation of this for a long time and I cannot get it to work.
I am trying to depending on the textbox selection get the proper ROW from DEALLIST to show up.
View 2 Replies
View Related
Sep 8, 2007
In a large spreadsheet that receives external data, I have codes (U, N or V) allocated at various times to different rows as shown in sample worksheet attached. Elsewhere in the spreadsheet, I need to display (for later export) these codes along with their respective number in a sorted list. Please review the attached:
In the spreadsheet you'll see a column of codes, the next column is the data reference number, then to the right is three columns, one for each code. As displayed in the sample book attached, each Code column is to display the data reference number (from column 2)that matches that column's code.
What formula can I use to list these numbers in the appropriate columns?
View 5 Replies
View Related
Apr 5, 2004
I am trying to search a range for all of the nonblank cells, when I find one, return the column header and the value in that column. Vlookup won't work on this. It will only return a specified column.
Employee performance
ID area one | two | three
12947 23 | | 19
12948 32 | | 20
12948 |17 | 52
What I am trying to do is create a communication sheet. Employee ID 12947, your performance in area one= ______, in area two = ______ and so on.
View 3 Replies
View Related
Aug 22, 2013
i have a large table and i need separate x and y coordinates(to plot on a graph) of a particular repeated value that is known to me in a large table of numbers. how can i do this?
View 4 Replies
View Related
Oct 7, 2008
I'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.
View 2 Replies
View Related
Apr 5, 2014
I have my data arranged in a table on sheet1 as shown below.
Columns B to H contain the hours each person normally works on the days of the week Sun to Sat. In columns I to O are the hours that each person has booked as holiday for that week.
On sheet2 I would like to be able to enter a holiday day in C1. In A3 to A15 I would then have a list of all the people who will be working on that day and in C3 to C15 have a list of the people who are on holiday so the output on sheet 2 would look like this.
Basically I'm after the formulas for A3 and C3 that can be copied down the column depending on how big my table gets. I am using Excel 2010.
View 9 Replies
View Related
Oct 5, 2012
I have sheet 1 that in cells V5:V20 is a data validation drop down box. In cells W5:W20 I have another data validation drop down box. On sheet 2 I have a table that in column A matches the data in the drop down box's in column V on sheet 1. Row 1 on sheet 2 data matches the data in the drop down boxes in sheet 1 cells W5:W20. What I'd like to do is on sheet 1 Cells Y5:Y20 have a VBA code to lookup the data in columns V and W from sheet 2 and return the value.
Windows 7
Excel 2010
View 9 Replies
View Related
Oct 30, 2013
In the attached sample file, one can see the setup of my data. I'm trying to reference the row number of a certain cell in a matrix with many cells of the same value. I have different Headers of each row, which should make it possible to use INDEX, MATCH or VLOOKUP. but i don't seem to be able to get it right.
Description.I need to return the "class" och each of the capability elements, i.e. If the capability in "Soups" is marked ("X"), at class 3, i need the integer "3" to be shown here.
View 12 Replies
View Related
Apr 18, 2013
I have a phone list and I am trying to add a search box to it that when you type in the Employee name, it will return the value of the cell adjacent to the search result so the extension can show up.
I am a VBA Novice, but I have found plenty of code for the searching aspect of it. I am having trouble figuring out how to have the search occur and then return a different value then what was originally searched for.
Ideally I would like the msg box to show both the employee's full name and extension.
View 4 Replies
View Related
Jun 13, 2006
I 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.
View 12 Replies
View Related
Apr 7, 2009
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
View 9 Replies
View Related
Oct 17, 2006
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...
View 3 Replies
View Related
May 12, 2014
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.
View 1 Replies
View Related
Jan 9, 2009
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?
View 11 Replies
View Related
Nov 11, 2009
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)?
View 9 Replies
View Related
May 7, 2013
I 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
View 6 Replies
View Related
Sep 30, 2009
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.
View 12 Replies
View Related
Dec 14, 2011
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 Related
Jun 13, 2013
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","")
View 3 Replies
View Related
Apr 8, 2013
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
View 1 Replies
View Related
May 15, 2014
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.
View 3 Replies
View Related
Jun 17, 2009
I am having a littler trouble with using a function to find text (last name) in a table and then return full name from the table. See attached sheet with example of what I am looking for.
A quick explanation:
Have a table with columns that have team and name of player. My input is the last name of the player and the team. Need a fomula that searches all the table and then returns the full name of player based on 2 conditions of last name and team.
View 2 Replies
View Related
Dec 21, 2008
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.
View 2 Replies
View Related
May 17, 2006
I am trying to write code so I can control the PivotFields on a PivotTable from another sheet in the book. I cannot figure out how to get Excel to return a list of the fields in each page field. If I record code to change the Animal PivotField to show dogs, i get the following:
ActiveSheet.PivotTables("PivotTable1").PivotFields("Animal").CurrentPage = _"dogs"
I can also choose cat, birds, and beavers, but I do not know how to get excel to return a list of the option in the PivotField.
View 2 Replies
View Related
Dec 13, 2007
Need to do (preferrably as a UDF so I can call it many times and build the macro I really need) the following string testing macro. For a given column (B) - look to the field immediately to the left for a text string. This string will be searched for a list of other strings contained in Column D ( cells D1:D5). We want a case insenstive SEARCH (or find I suppose in macro terms) to return a true if the strings are found or false if it is not. Example test string in cell B1 would be:
I am a string of test data to check.
Terms in D1:D5 might be:
dog
cat
bird
data
fowl
Cell B1 should return a TRUE as a match.
View 2 Replies
View Related
May 7, 2014
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.
View 8 Replies
View Related
Dec 23, 2008
The idea is to have our technicians complete a timesheet showing how their time is being allocated between various tasks. I have created the spreadsheet and each row has a drop-down list with 6 options (Project Management, Audit, Office, etc) then a start and finish time which is calculated. I want to automate it so each each task option (Project Management, Audit, etc.) is calculated. There will be multiple entries for each task option over a weekly period. I tried VLOOKUP, but it does not work well with the text entries. I have attached a copy of the spreadsheet to better illustrate what I am trying to explain.
View 4 Replies
View Related
Dec 1, 2011
Searching a text string on Sheet 1 to see if it contains any item in a list (on Sheet 2 Column 1) and if so return the value of Sheet 2 Column 2 next to the list item found?
It's a budget problem: Sheet 1 has my downloaded Visa statements. I want to categorise all items.
Eg Any item containing the string 'safeway' is categorised as 'General expenses'. So the list on Sheet 2 has an item called 'safeway' and in the next column 'General expenses'.
And for the item on Sheet 1 'BPAYN BUPA AUSTRALIA BPAY MBF monthly' I have an item in Sheet 2 that is simply 'BUPA' with category 'Medical expenses'.
View 6 Replies
View Related
