Search Table With Multiple Criteria And Return Row
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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Search Multiple Worksheets Against List Of Non-Exact Search Criteria?
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.
View Replies!
View Related
Search A Table, Return An Array
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 Replies!
View Related
Return Cell Containing Search Criteria
If I have a 'key' value in a cell in one sheet, i want to use that value to find the cell in another sheet containing the 'key' and return the row number of the cell, if more than one value then I would like to be able to loop through all the rows containing that 'key' value returning the row number of each hit, kind of a programmatic version of vlookup?
View Replies!
View Related
Vlookup: Search For Value In SECOND Column Of Table Array And Return Value From FIRST
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 Replies!
View Related
Search Table & Return Heading Of Found Value
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 Replies!
View Related
Search For Multiple Criteria
I have a spreadsheet with a database search function incorporated. Whilst this works fine, I was wondering if there is a way to combine the current single query searches into one search macro. I have setup a userform called CriteriaSearch that I would like to use to search my database tab. An example search would be: User requires someone who speaks French (5), is female, has a rank of 3, is available immediately and has notes/keywords/keyphrases of "excellent linguistic and cultural knowledge". The appropriate fields on the userform are populated accordingly. The user selects search and a macro runs to find matching records. To make things slightly easier, the criteria (Language, Gender, Rank etc) all have fixed column references.
View Replies!
View Related
Find To Search And Return Multiple Dates
I am working with a macro into which you input a date, the macro searches for that date (in column D of the data sheet), then copies any row with that date onto a new sheet. This works great, but now I need to be able to search for more than one date at a time and return any rows that cantain ANY of those dates. For example, I would want to search for any row containing 01/01/07 - 01/07/07 or any row containing 01/01/07, 01/02/07, 01/03/07...01/07/07.
View Replies!
View Related
Search Multiple Files & Return Corresponding Value
I have a bunch of quotes in Excel format which have 5 columns ; Part number, Description, Qty, Cost, Total Cost, Unit List,Total List. I need a piece of code to open many excel files, look for a "part number" beginning with ED,DS,AP,MP or MDS and return the corresponding value in the "total cost" column then record the value in a separate spreadsheet.
View Replies!
View Related
Lookup/Search Multiple Columns/Criteria
I am looking to write a search function that searches through a whole document. The only examples I can find are of a could different search functions but they all are searching an specified individual column and even then I didn't really understand them. My worksheet has 6 different columns which are all different labels for a certain tool. I want the user to be able to input any of those 6 labels and have all the information in that row be returned.
View Replies!
View Related
VBA Keyword Search To Return Row Numbers
i'm writing a few VB subs to help manage a database of mishaps, solutions and lessons learned, there are 11 columns and each databade entry has a separate row, there are approximately 1,000 rows. Columns are: ID- a unique iderntifier for each entry- integer Date- date the entry was made- date Project Number- a reference to an internal project file- string Operator- company we were working for- string Installation- where the work took place- string Category- type of problem encountered- string Application- field of problem encountered- string Issue- string Background- string Problems- string Lessons Learned- string...........
View Replies!
View Related
Multiple Search Criteria To Bring Back Data
I have a dataset which is dumped into my model every month. The Report tab is a "user form" whereby the user can select multiple search criteria. The dataset is then filtered according to the search criteria entered by the user. Problem is, that if the user only selects 1 of 3 search criteria and the other 2 cells are left blank, the filter filters on these blank cells and doesn't retrieve any data. Any ideas how I can overcome this? Sub PopulateReport() Application.ScreenUpdating = False Dim MyFilter1 As String Dim MyFilter2 As String Dim MyFilter3 As String MyFilter1 = CStr(Sheets("Report").Range("C2").Value) ' convert cell value to string MyFilter2 = CStr(Sheets("Report").Range("C4").Value) MyFilter3 = CStr(Sheets("Report").Range("C6").Value) Sheets("Waste").Select Dim Rw As Long Dim Rng As Range Rw = Range("A65536").End(xlUp).Row Set Rng = Range("A1:W" & Rw) With Rng .AutoFilter .AutoFilter Field:=20, Criteria1:=MyFilter1 .AutoFilter Field:=2, Criteria1:=MyFilter2 .AutoFilter Field:=13, Criteria1:=MyFilter3 End With how to display my excel worksheet using HTML maker as I've downloaded the software but have no idea how to use it!
View Replies!
View Related
Vlookup Formula That Incorporates Multiple Search Criteria
Would like to be able to use a vlookup formula that incorporates multiple search criteria. In C10, I have a specific rank listed. In C11, I have the supervisor listed. The formula needs to list the name of the agent in C12 based on the criteria in C10 and C11. agent rank score supervisor A B C D 1 leo 1 99 jim 2 bob 3 68 jim 3 sam 1 73 ann 4 tim 2 84 adam 5 alf 2 55 ann 6 dan 3 42 adam 7 will 2 79 jim 8 sara 1 91 adam 9 10 Rank: 1 11 Supervisor: adam 12 Agent: " "
View Replies!
View Related
Match Criteria And Return Value From Different Row
I have 2 rows of data and want excel to find the number of times that a number appears in the first row and then return the value of a cell in the same column but in the second row of data. I need it to repeat this until all matches in row one, and their corresponding number in row 2 have been found and then add all the results from row 2 together to give a single numerical answer. I have tried the ' lookup' function but this only returns the first number that matches the criteria and does not continue to find the remaining matches.
View Replies!
View Related
One Criteria - Return Multiple Records
I have a spreadsheet with multiple columns and rows of data. I want to be able to type in a criteria and all the rows containing the criteria are called up. For example Col A Col B Col C Row 1 Apple Fruit 12 Row 2 Banana Fruit 15 Row 3 Carrot Veg 13 I want to have a cell on another sheet in which I can place a criteria, eg Fruit, and then the entire row 1 and 2 are displayed on the second spreadsheet.
View Replies!
View Related
Return Row Number That Matches 2 Criteria
I have a produced an Excel workbook which uses a VBA sign in/out userform. Once you sign in on the Userform the sheets update. A list is completed of the times people enter and leave. To make the code easier I currently have the name being returned to the excel sheet and performing a “match” function to return the row number. This row number is then used to carry out what I need to happen in this row. However, as you can see from attached doc (and the brief example below), based on IDnumber "2", the match function returns row 5 not row 8. I need to have the row number returned for the IDnumber where the Out cell is blank. This should be the last occurrance of the IDnumber Example Row 1 ID# In Out 2 1 9:00 13:00 3 4 9:00 13:00 4 3 9:00 13:00 5 2 9:00 13:00 6 5 9:00 13:00 7 6 9:00 13:00 8 2 14:00
View Replies!
View Related
Return Multiple Values To A Table And Count
I have a spreadsheet with two different rating scales (People & Business) that have a value of 1-5 per person. From this I created another column 'Sorter' that gives a person a single value of 1-25 (5*5 possibilities from the two rating scales.) I am trying to place people into a table based off of the column 'Sorter' as shown in columns U-Y. The real table cleaned up is in the table tab.
View Replies!
View Related
Return Result Based On Multiple Criteria
I have been trying this for hours but to no avail. I have a table with 4 columns headers Name, Amount, Loc and Code The name may look like ABC 1, ABC 2.... The Loc may be in US, GB... and the Code may be AA, BB I need to return a result "Y" if the sum of the amount is > 100 and "N" if the sum of the amount < 100 based on the conditions of the following :if 1) Name is the same entity, such as ABC 1 and ABC 2 and 2) Loc is the same, US..and 3) Code is the same I have attached a sample to illustrates the result
View Replies!
View Related
Return Multiple Results Based On Criteria
Please see the attached sheets. Here's the issue: On sheet "master" I have a list of goals in column A. Each has been assigned to at least one person. Each person will have their own sheet that will capture the goals assigned to them. As an example, see Al's sheet. I am trying to get Excel to look for all of the goals assigned to AL on the Master sheet and list them nicely onto AL's sheet without out blank rows. (and for each, respectively on their own sheet). I could do this with Pivot Tables if the data were displayed differently, but I have been told that I have to display the goal assignments as shown. I could use "x"s instead of their names to mark the assignment and I could combine the "goal-achieved expectations" and "goal #s" columns if it helps. I am not supposed to use filters either. Note: Of course, a goal or an assignment could change on the master sheet, but the assignment is more likely to change. how this done for AL, I can go ahead and create the other sheets.
View Replies!
View Related
Return Value By Multiple Matching And Unmatching Criteria
I am developing Spreadsheet that you will enter a list of parts that you need. it will have many columns but only 4 are vital to the check. this will be generated for each project. With each part i want to then look up in another file that is a list of all the possible parts you can have with there cost. this file will be standard for everything and it will be updated from time to time. now not all the criteria to look up will be exact matches. here is an example code temp/pres rating diameter material GLV 4500 15 16Mo3 in the other file we will have multipu entries for GLV with different values. code temp/pres rating diameter material Cost Reference GLV 4500 20 16Mo3 400 aaa1 GLV 5500 15 16Mo3 350 aab1 GLV 5000 15 16Mo3 300 aab2 GLV 5000 15 13CrMo4-5 600 acb1 GLV 5500 15 13CrMo4-5 600 acb2 For Code i need it to match (there are lots of codes even including variations like GLV.) For Temp/pres rating i need it to be equal or higher to the value For diameter i need a match for material i need a match. so as you can see from the list above there are 2 options. so then i want it to return the cheapest. that is step one. step two is to repeat it for multiple list of parts.
View Replies!
View Related
Extracting Data From A Table - Multiple Criteria
I have a data table that has the following structure Team manager Warrington 10 12 14 16 Team manager Liverpool 8 10 11 12 Commercial Manager Warrington 25 28 32 33 I need to extract the data out into a flexbible summary table where i can choose the job title, location and year. I can use the following index / match formula to extract the salary that matches the job title and location. (INDEX('Emp In'!$c$5:$c$130,MATCH(1,('Emp In'!$A$5:$A$130=$B3)*('Emp In'!$b$5:$b$130=$C3),0) - Where b3 = job title, C3 = location, Column C = Salaries year 1 However I cannot add the flexibility to choose a year as this formula fixes the index on a chosen column (yr 1 column C is this instance)
View Replies!
View Related
Fill In Table Based On Multiple Criteria
I would like to fill in a table based on a small database this table then creates a graph. But I need to look at 3 criteria before I can fill in the table namely: customer - weeknumber - weekday based on these items the time should be filled in accordingly. Can this be done with regular formula or should I try VBA ? please have a look at my file the data should go from sheet input to sheet ACL based on the above mentioned criteria
View Replies!
View Related
Find Value In Table & Return Column / Row Header
I am having trouble with getting a value (which is the row and column value) from a set of numbers. I have attached an example table to try and make things clearer. What I have is a table with a set of Row Headers, and Columns Headers all numeric. e.g Columns headed 1, 2, 3, 4, Rows 30, 40, 50 etc. I want to find the 1st occurrence of a value e.g. 1.0 in the table (by 1st Up mean closest to top left), and then give me the value of the Row and Column Header that corresponds to that. So for the example attached, I want to get the row Value 200 and Column 6 from finding the 1st occurrence of the number 1. I have tried various combinations of index and Match etc. But I can't get my head around the fact that the position of the 1st no. 1.0 can change, therefore I can't use any releative positions etc.
View Replies!
View Related
Return Multiple Values Across Columns And Rows Based On Criteria
I have a table with column headings of product ID Numbers (eg.1111) and row headings of Store number (Eg.1) with data showing the time each product was last sold at that store, I need something to consolidate for each store which Product ID's were sold prior to 5pm and what time they were sold. EG Store 1 1111 16:40 2222 13:00 Store 2 1111 15:05 3333 16:50
View Replies!
View Related
Formula/Function To Return Multiple Values Based On Criteria
I would like to be able to use the Vlookup function to return more then one value as a result of the criteria. I have a cloumn of dates that populates the X axis of a gantt chart with data whilst the Y axis will be populated by a site reference resulting in a program of work, the Y axis data is the result of a vlookup function. My difficulty arises however with multiple sites, for instance where two or three sites will be visited on the same day. The vlookup function will only return the first value it finds in a range to the formulated cell. The result being a missing site(s) from the gantt chart / work program. is it possible to return all values to a cell i.e. site1, site2, site3. using a vlookup or do i need to use another method of doing this?
View Replies!
View Related
Delete Row Based Upon Multiple Criteria
I am attempting to create a macro that will delete an entire row, based upon multiple criteria. The data looks something like this: -----Column A-----------Column B--- 1.-- Computer_X ------- Software_A 2.-- Computer_X ------- Software_B 3.-- Computer_X ------- None 4.-- Computer_Y ------- None 5.-- Computer_Z ------- Software_A 6.-- Computer_Z ------- None I would like to delete the entire row if: 1) Column B entry is "None" AND 2) Column B has entries other than "None" that match Column A So I do not want to delete a row with a unique name in Column A and "None" in Column B. I only want to delete the rows that have "None" in Column B, and multiple entries for the match in Column A. Using the data example above, Row 3 and 6 would be deleted. Row 4 would not be deleted, since Column A (Computer_Y) had only one row of data, with "None" entered in Column B.
View Replies!
View Related
Multiple Criteria Row Deletion Code
modify the delete row macro below. Unfortantely this is where my brain cell sometimes dies and the easy answers are as apparent as they should be... Ideally what I am looking to do is have a growing list of "IDS" in one worksheet (call it delete IDS) that will be used to loop thru the sheet named "NRs" and delete the the IDS from that worksheet. Sub DeleteNames() 'using set column Sheets("NRs").Activate Dim RngCol As Range Dim i As Range Set RngCol = Range("A:A") For Each i In RngCol If i.Value = 156916233 Or i.Value = 600093 _ Or i.Value = 156970571 _ Or i.Value = 156948409 Or i.Value = 30560 _ Or i.Value = 163841503 Then _ i.EntireRow.Delete Next i End Sub
View Replies!
View Related
Lookup With Multiple Criteria: Return The Amount Paid And Full Cost Based On The Person's Name And The Date
I would like a lookup that takes multiple criteria and that is not an array formula! Unfortunately I decided to use array formulae and my spreadsheet went to over 45mb!! Not good. I've searched the forum for an answer to my questions but couldn't find any! I've attached a spreadsheet as an example. The examples I am using have {Sum(IF)} formulae in it (array) and I would like to change those to others that will not increase the file size so much and will not take too long to calculate. Basically, I would like a lookup that will return me the Amount Paid and Full Cost based on the person's name and the date. the data and the results table are both on separate sheets. It would be nice to bring that file's size back down to less than 4mb!!
View Replies!
View Related
Lookup Multiple Columns And Return Top Row
I've been working this for ever and can't seem to figure out the best way to go about it. I have attached an example sheet. All I need to do is figure out the Dept #... which is listed in Row 1, Column F:H. I want to match the project numbers and then return the AA, BB, or CC in Column B.
View Replies!
View Related
Index & Match Formula: Multiple Row Criteria
I am trying to get my INDEX & MATCH formula to retreive data from my table. This is what I can do so far: Jan-07Feb-07 100 12 250 45 =INDEX(table,MATCH(B13,balance),MATCH(C13, date)) But I am trying to get it to get another row to look up as well. I want it to look up the color then the 100 or 250, then the date. Jan-07Feb-07 red100 12 red250 45 blue100 78 blue250 1011 =INDEX(table,MATCH(B13,balance),MATCH(C13,date)) I think i need to insert another match code in the row section but cant seem to get it to work.
View Replies!
View Related
Macro Code: Find Cell Or Row Multiple Criteria
I can't seem to find the answer to this. In VBA, how do you Find a cell or row based on multiple criteria? All I need is to find it's location. If it helps, here's an example: Sheet 1 contains source data: Date Name Time Complete Sheet 2 contains an interactive worksheet using this data. On Sheet 1 there's an entry: 9/12/2006 George 03:30 PM Sheet 2 displays those records with adjacent cells for entering data. When data is entered, I need the system to go back to Sheet 1, find the entry that matches this one, and put an "x" in the Complete column.
View Replies!
View Related
Lookup Multiple Criteria & Sum Result On Multiple Criteria
I am trying to solve a problem. I am currently using this formula = SUMPRODUCT(--(Sheet2!B2:Sheet2!B300="MARKETING"),--(Sheet2!D2:Sheet2!D300="200612"),Sheet2!E2:Sheet2!E300) This formula works for me as it is but I would like to add more months to 200612. I want this to also be 200701 and 200702. In another cell there will be up to 10 months. Is there a way to do a Vlookup or something that will look up these months in another table, rather than keep typing them out in the formula?? Otherwise my formula will be very long. So the info looks like this in excel MARKETING 200612 -10 MARKETING 200701 -25 MARKETING 200708 -50 ECONOMICS 200709 -30 The info goes on and on. The two variables are the MARKETING column and the month column. My problem is that I would like a seperate table that can be the months. So 200612 and 200701 is one table, and 200708 and 200709 is another table. The table changes often so I dont want to mess with the formulas, rather a table.
View Replies!
View Related
Using The =IF(ISNUMBER(SEARCH Function To Search Multiple Cells)
I' having trouble using the =IF(ISNUMBER(SEARCH formula to search multiple cells. I can get it to work to search one cell (as below): =IF(ISNUMBER(SEARCH("Same",G10)),"No Change",'Aug 09 Matrix'!F10) (In this case the respone is No Change as Cell G10 contains "Same") However cannot get it to work for several cells. I have pasted below and highlighted the function I would like it to perform: =IF(ISNUMBER(SEARCH("Same",G10:R10)),"No Change",'Aug 09 Matrix'!F10) Essentially the other cells in that row (G10:R10) all contain "Same", however for some reason it is identifying this as FALSE and putting in the data from the 'Aug 09 Matrix' sheet.
View Replies!
View Related
Find Multiple Instances Of Single Criterion In Row & Return To A Single Col
Find Multiple instances of Numeric Criterion in Row & Return To a Single Column. I have a Dynamic Named Range "Data" spanning 10 Columns and many Rows. Each Row may contain duplicates of the Numeric Criterion. I would like to find ALL instances of a specific Numeric Criterion across each single Row in the Dynamic Range "Data" and have the Results returned to a New Sheet in a single column. NEW Sheet: The Numeric Criterion is housed in G5. The matched criterion should be returned to the New Sheet starting at G7. Duplicate instances in the same Row should ALL be returned to the same cell in Column G on the New Sheet. Sample Data Layout: Columns I J K L M N O P Q R Row No.76 1 0 1 1 0 1 1 1 0 1 Row No.77 2 2 3 2 1 2 2 0 0 0 Row No.78 3 3 3 3 3 0 3 0 3 0 Scenario: Looking for Numeric Criterion 1 (one). Expected Results - New Sheet: Row No.7 Column G (Cell G7) 1111111 Row No.8 Column G (Cell G8) 1 In Row 76 of the Sample Data ALL seven Numeric Criterion of 1 (one) should be returned to the same cell G7. In Row 77 of the Sample Data there is only one Numeric Criterion of 1 and it should be returned to cell G8.
View Replies!
View Related
Combining Multiple Cells In Multiple Worksheets In Multiple Workbooks Into One Table
I'm currently doing a survey using an excel workbook that contains multiple questions across multiple worksheets using radio buttons linked to certain cells. I have around 400 workbooks coming back to me, so what i want to do is take specific values from across many worksheets within each workbook and combine them into a large master table in a seperate workbook. I've tried using VBA, but not being very proficient at it i've hit a brick wall with that, so i'm hoping that there is an easier way to do it than what i'm currently pursuing.
View Replies!
View Related
Search And Return A Value
In the attached file, I am trying to lookup what vendor is assigned to a part, and enter the vendor number in column J, K or L, depending on the number that is assigned. For example, the top part number, RAD001, has a 2 in column N, indicating vendor # 015354 is the Alternate vendor # 1. It has a 1 in column V, indicating 034180 is the Primary Vendor, and it has a 3 in column Z, to indicate that 015229 is the Alternate Vendor # 2. What formula would I need to put in cell J2 so that if a 1 is found in row 2 it will return the value 1 position left of where the 1 is found? I will then put a similar formula in columns K and L to return the vendor number if a 2 or a 3 is found. The end result will be that for each part number you will be able to see what vendor is primary, alternat 1 and alternate 2. I want to make this a formula because the actual sheet will be thousands of part numbers long, and I will need it to update the vendor information if the primary vendor is changed.
View Replies!
View Related
Fill Table By Match Row & Column Headings Of Another Table
I currently have a table with a range of headings (row & column), and the necessary data for it. On a new worksheet, I have a table with only a few of the headings, and I was wondering if there was a macro that would automaticlly match the headings of the new sheet with the other table, and fill in the ncessary dat, as on the other sheet.
View Replies!
View Related
Compare Two Cells In A Row With Criteria. Count Rows That Match Criteria
Here is what I have. 4 Worksheets. The first worksheet is a summary page. I have 350 personnel that are broken down into three different groups. So each group has it's own sheet. Here is what I need to accomplish. Results need to be posted on the summary sheet. I need to compare cells B2 & D3 for each row on a worksheet and display the number of times they match on a worksheet. For example how many times does EP & EP match on a certain row. I need to compare cells B2 & D3 for each row on a worksheeet and display the number of times they don't match on a worksheet. For example how many times does EP & MP occur. I've attached an example for reference
View Replies!
View Related
Pivot Table Chaos: Blank Cells Return 0, Empty Cells Return Nothing
Hi, I desperately need help with a pivot-table and can't find anyone else who's ever had this problem...I have a very large healthcare data table which for simplicity I will describe as follows. In essence the first two columns respectively are (A) location and (B) month. Let's say the third column (C) is the number of cases of the disease kidpox treated in each health centre during that month. The fourth column (D) is the total number of cases of everything treated during that month in each clinic. The fifth column (E) is the proportion of cases of this disease over all consultations for each health centre and month, and the formula in E2 is .....
View Replies!
View Related
|
TRACKER
