 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / March 2008Can Excel return multiple correct answers?
Good morning. In all my years, I’ve not come across this. Searched the Board; there’s a post suggesting drop-down lists, but that would require too much ongoing manual input (I think). 1) I have 2 criteria that must be met 2) I have a table of data that includes many rows of data that will meet the criteria, many that will not. How do I get Excel to extract all correct answers? The problem, as I see it, is that I input my request for this in one cell, thus limiting myself to one answer. I’m not so sure the answer is to copy that static criteria down a bunch in one column, and then do a V-Lookup, because there are multiple unique 2-criteria data sets with corresponding multiple possible returns. That means I’d have to copy a bunch of different 2-criteria data sets and V-Lookups (hundreds). Do I finally have a reason to learn Access? I have Excel 00, 03, and 07. I sense I’m gonna learn something new today. Thanks! Check out data>filter>advanced. HTH > Good morning. > [quoted text clipped - 17 lines] > > I sense I’m gonna learn something new today. Thanks! Hey Jim – Wanted to get back and acknowledge your advice; been sitting here reading. This is really interesting. If I’m reading the 2000 manual correct, it looks like I can have multiple unique 2-criteria data sets instead of just one, static 2-criteria data set. And that’s by putting the criteria on the SAME row? And then I can have a bunch of those unique data sets going down the two columns? Really appreciate your help so far. sam > Check out data>filter>advanced. > [quoted text clipped - 21 lines] > > > > I sense I’m gonna learn something new today. Thanks! Debra Dalgleish has some notes on Advanced Filters here: http://www.contextures.com/xladvfilter01.html Easier to follow than in XL Help. Hope this helps. Pete > Hey Jim - > [quoted text clipped - 38 lines] > > - Show quoted text - That's pretty much the way it works. Start simple. Copy a column heading from your data to a cell off to the right of the data. Enter your criteria in the cell below the heading. These two cells are your criteria range. Then do Data>Filter>advanced and enter your data range and criteria range. Excel will return all the records in the data that match your criteria. After you get that down, you can add additional field headings and expand your criteria. It's a very strong feature. HTH Jim > Hey Jim – > [quoted text clipped - 36 lines] > > > > > > I sense I’m gonna learn something new today. Thanks! This is huge. Thanks to you and Pete. It occurs to me that that one of my criteria is not as static as I portrayed. The two criteria are Name and Date. The table of possible answers obviously includes name and date (as well as 3 other data items on the same row that need to be returned), BUT a correct return should include any row of data in the Table where there is a name match and a date that is plus/minus 14 days of the criteria date. I'll keep reading, but do you know if I can use an IF statement somehow? So if my criterion date is Jan 01, 2008, which is 39448, any date in the table of possible answers between 39434 and 39462 is a correct answer. As long as there is a name match, of course. > That's pretty much the way it works. Start simple. Copy a column heading > from your data to a cell off to the right of the data. Enter your criteria [quoted text clipped - 49 lines] > > > > > > > > I sense I’m gonna learn something new today. Thanks! You can use two date criteria cells. In one, put '>=1/1/08 and in the second put '<1/15/08. You would put these side by side with the date field heading above. HTH > This is huge. Thanks to you and Pete. > [quoted text clipped - 64 lines] > > > > > > > > > > I sense I’m gonna learn something new today. Thanks! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.