Search Criteria With Multiple Answers
Apr 9, 2012
On my spreadsheet i want to find the results from 2 criteria that i entered.
My search criteria are "Oostbos" and "AA8", and excel has to find this from another spreadsheet that i made for rostering.
OostbosN3 evelineAA8N3 evelineAA8N2 MargaAA7
The problem is that i have multiple shifts with the "AA8" criteria, but my function only finds the first one.
I used the following function:
Code:
=IF(C8="";"";INDEX('Afdeling PG PH'!$A$6:$A$28;MATCH(C8&$A$7;'Afdeling PG PH'!$B$6:$B$20&'Afdeling PG PH'!$AI$6:$AI$20;0)))
Also when the AA8 cel is empty that i doesn't show anything.
How the second N3 eveline, shows the 2nd result and so on.
View 1 Replies
ADVERTISEMENT
Oct 25, 2007
I have a workbook filled with many tabs / sheets. Each sheet contains the same structure of information and isn't an important aspect of this question.
I've looked at this topic http://www.mrexcel.com/board2/viewtopic.php?t=16683 and I'm unable to follow it well.
Is there a way to search all the tabs for a word and if it matches to copy the entire row into the search tab / sheet? Specifically for each and every searched match.
It would save my users trying to search each tab individually and possibly miss the data they are actually looking for. Hundreds of people use this reference workbook which stores where all our documentation is stored across numerous servers. Each row is one document and its corresponding storage information.
View 9 Replies
View Related
Nov 2, 2009
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 3 Replies
View Related
Apr 28, 2012
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 #.
COLUMN ACOLUMN BCOLUMN
C11111AlaskaProperty11111AlaskaCasualty11112AlaskaLife11112AlaskaHealth11112
AlabamaProperty11112AlabamaCasualty11113ArkansasLife11113ArkansasHealth11114
ArkansasLife11114ArkansasHealth12345ArizonaProperty
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
View 5 Replies
View Related
Sep 28, 2012
Want a single count of multiple columns based on the columns selected value. Data is in text format.
Have tried multiple COUNTIF statements and have tried using pivot table (Excel 2010) both only give me total counts for all. I think I need an OR statement somewhere, but not sure where?
In other words, if a single record has an "any" in the any fields or a "yes" in the yes fields, I want to to count that as one record.
Sample data:
Pegnancy Smoke
Pregnancy Alcohol
Pregnancy Marijuana
Pregnancy Powder
Stress Cigarettes
Stress Marijuana
Stress Alcohol
Stress Medication
[Code] .....
View 2 Replies
View Related
May 22, 2009
I am trying to search for multiple values in a cell with Multiple criteria.
E.g: Please find attached the sample excel data.
I tried using VLOOKUP which is not working as I am having multiple values in column A.
View 9 Replies
View Related
May 8, 2006
here's the formula that I'm using: =INDEX($C$35:$AF$35,MATCH(MAX(C41:AF41),C41:AF41,0 )) where $C$35:$AF$35 contains names of people & C41:AF41 contains #'s. This works great if there isn't a tie. Is there a formula to search the range, find the max # and if there are two answers display both names. ex. the max# is 3 and both joe and sam have 3. it would then show joe / sam or something like that. Is this possible? If not, then if there is a tie for the max #, to have the cell just display tie.
View 4 Replies
View Related
Feb 14, 2010
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 2 Replies
View Related
Jun 26, 2008
I have a table whereby I Vlookup a different spreadsheet on an order number (in this instance Z011352/001). There are multiple sketch numbers associated to this Z number.
I would like to be able to input the Z number and then obviously the Vlookup pulls up the first sketch number. This works fine.
If I then input the same Z number again it pulls up the same sketch number, but I want it to look in the current table (the one i'm entering the Z number onto), see if the sketch number is already present and if it is pull up the next sketch number in the list on the separeate spreadhseet. (I hope this is clear enough)
My Vlookup code is:
=IF(C1070"",VLOOKUP(C1070,'[CrossingsSchedule.xls]Update Worksheet'!$E$1:$AC$10000,20,FALSE)," ")
View 9 Replies
View Related
Jan 12, 2009
I have a message box in my spreadsheet. I want it so if the user puts in multiple answers that certain message box appears, if they put in a different set of multiple answers it shows up. For example, the code below is what I am using now. It says that if the user types 5225 in C16 and 3000 in C17 then a specific msg box appears. I want it to say if the user types in 5225 or 5226 or 5227 the same msg box appears but if they type in 5665 a different msg box appears. How would I change this code? ...
View 9 Replies
View Related
Feb 26, 2014
I am looking to search in a table (say 4 columns) corresponding to multiple criterion (one for every column except fourth) and returning the values which are numerous (from column 4). I have tried the INDEX function but it only gives me one of the many cells. I am working on a table with +20000 cells per column
View 3 Replies
View Related
Jun 21, 2013
I need a formula that will tell me what Tracking # in the spreadsheet has multiple dispositions and what those disposition are. I need the formula to use column and cell coordinates and not using word searches.
example
Tracking # Req.# Disposition
1490069 045 snakes
1015907 047 candy bars
1488026 044 chickens
1015907 047 sand paper
1015907 001 fruitcups
1490129 046 cupcakes
1484817 043 Cats and dogs
View 2 Replies
View Related
Oct 5, 2011
I want to display multiple results based on 2 search parameters, where by when I enter flight number and Dep. Airport it matches the trip numbers and dispalys all the rows matching all flight number and Dep. airport based on the unique trip number.
Notice: - one trip number could consist of multi flight sectors.
1) Explanation of the data :
MMM1805 = flight Number
935 = dep. time
$AS41 = registration code
[Code].....
View 9 Replies
View Related
Aug 29, 2013
An example of my data is:
Column A Column B Column C
ABC DEF Answer 1
ABC GHI Answer 2
ABC JKL Answer 3
I need a formula that checks both column A for text (ABC) and column B for text (JKL, two different criteria) and give 'Answer 3'. There will a lot of data and it will not always be the bottom answer.
View 2 Replies
View Related
Oct 30, 2013
I have thought about recording a vlookup but this might be messy and prone to errors. Is there anyway to search through multiple sheets for a value (code) in Col A sheet 1. And return X in sheet 1 in the next column.
View 2 Replies
View Related
Jun 4, 2014
In the attachment, on the totals sheet I am doing a count of the results on Sheet2. Under "Alcohol as it Applies to Me" on Totals I am trying to count the 5 different categories, but the original question is a pick all that apply so at times there are multiple answers. I can't figure out the formula to count each phrase when there is multiple answers.
HRA Results.xlsx
View 2 Replies
View Related
May 6, 2014
I am using the formula =IF(OR(E2="COLD"),"33%")
Which changes my cell to show the text 33% if the text cold is entered into cell E2. Now what I would like to know, is if I can add multiple catch words to give alternate pre defined percentages. Such as warm and hot to give the respective answers as 66% and 99%
View 3 Replies
View Related
Jan 13, 2009
I am currently maintaining a database that keeps up to date records of employees in my company and their vacations including their nationality dept. etc. for the vacation reports i have a "last day of work," "return to work date," "Actual Return to work date," and at the end a "remarks" column,
Moreover I need to report how many employees per department/Discipline are on leave ex. ( mechanical, electrical, and so on.) That I did using countifs having whoever is remarked as "na" vs. actual return date, Discipline vs. each discipline. All works fine but what i want to ask is there anyway that i can list the names of employees that are on vacation under each discipline?? Ie. if 3 are in the electrical engineering department, can i list their names? or if Today()>Actual Return to Work day (ie they are late and have not arrived yet) is there a way that it can list the names of multiple employees? rather than having to work against each name etc.
View 10 Replies
View Related
Jun 9, 2014
I'd like to extract the data from Sheet 2 (Data) that falls within the selected date range but the formula I've entered in F$9 (see below) is giving me an error
=IF(ROWS(A$9:A9)>$A$5,"",INDEX(Data!A$2:A$387,SMALL(IF((Data!$A$2:$A$320>=$A$2)*(Data!$A$2:$A$320<=$B$2)*(Data!$B$2:$B$320=$C$2),ROW(Data!$A$2:$A$334)-ROW(Data!$A$2)+1),ROWS(A$9:A9))))
test1.xlsx
View 3 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
Jul 31, 2014
I have created a userform which searches from the data using the value that i put in text box.
E.g. if i put student i.d in textbox1 all other textbox show me the data like name contact detail etc.
But I want one more criteria. i want to add one more textbox to search for e.g either i put student i.d or locker no whereas right now i can only put student ID.
View 1 Replies
View Related
Sep 24, 2013
I have 2 columns ,A and B, in a table and text strings in C. Each table row A1 and B1 needs to be compared and matched with the text string C:C. Only if both, A1 and B1, are found in the same string, say C5, it has to return A1&B1. If not, return "Not Found".
Several Obsticles:
1. A and B are text and number
2. C is text with different numbers saved as text and words all mixed up
3. There is no defined format for the text, it is different in each string
A
B
C
-500.000,00
Banana Apple
Data: 1 MANGOGrape 0000000 800,000.00 EUR something something
-800.000,00
Mango Grape
Something your Ref: 8750ours R ef: TG0PPM000000743 500,000.00EUR zu Gunsten Banana Appl e VVA
With all these different formats and inconsistency, is it even possible?
View 2 Replies
View Related
Apr 9, 2008
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 4 Replies
View Related
Jul 2, 2014
Instead of trying to explain my challenge, the attached workbook should be self explanatory. My answer is surrounded by the box. I need a formula that would automatically provide this output.
Lookup Scenario.xlsx
View 9 Replies
View Related
Mar 4, 2014
I have 2 worksheets, Worksheet 1 has Customer Magic Number on it as a reference and a few customer details and Worksheet 2 has Customer magic number and contact fields.
I am currently using the formula
=INDEX($C$3:$C$575, SMALL(IF(N2=$D$3:$D$575, ROW($D$3:$D$575)-MIN(ROW($D$3:$D$575))+1, ""), COLUMN(A1)))
to show the contact codes in sheet 1 however I also need to show the Notes which are located in Columns G:I, Is there an easy to use the index & match functions as above with the concatenate function to add the notes in the cell beside where I am inputting the contact codes?
View 2 Replies
View Related
Mar 28, 2008
I have a table in excel that i need to use to mark a 20 question quiz. I have the correct answers in one column and the students answers in the next column. I want to mark a correct answer with 1 and an incorrect answer with 0 marks. I know how to use IF, however the students answers can vary eg, correct answer could be B and D but the student writes B + D. Is there any way of marking this correct even tho it is not exactly written out correct?
View 6 Replies
View Related
Oct 6, 2008
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 2 Replies
View Related
Jul 13, 2007
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 9 Replies
View Related
Nov 28, 2012
Attached is a sample workbook, but essentially what I'm looking to do is automate the process of searching through a data set where the value of interest (in this case, names) often has multiple entries, with different values attached to each instance.
I would like to be able to get a list of all values in a given column that match a specific name in another column.
Currently I'm using a basic INDEX/MATCH search just to see whether the data exists at all, but that's only half of what I have to do here, and I'm totally stumped on how to get a comprehensive list of all matches.
For reference, if you look at the sample, what I need is a list of all values in the "CPT" column that match the name searched for in the first column.
The actual data set size is at most 3-400 entries, if that makes a difference in how to approach this.
CPT Sample Book.xlsx
View 4 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
