How to list the rows that match a particular query?

I have a worksheet with a lot of rows.  I'd like to see all the rows
in the worksheet that match a particular "query", like "column A =
'foo' || column B = 'bar'". How do I do that sort of thing?
0
david
5/4/2010 9:49:15 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
661 Views

Similar Articles

[PageSpeed] 10

data>filter>autofilter>>>>

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"david.karr" <davidmichaelkarr@gmail.com> wrote in message 
news:22f3d35b-110b-4007-af74-73ad28f483f0@s29g2000yqd.googlegroups.com...
>I have a worksheet with a lot of rows.  I'd like to see all the rows
> in the worksheet that match a particular "query", like "column A =
> 'foo' || column B = 'bar'". How do I do that sort of thing? 

0
Don
5/4/2010 10:31:56 PM
Use Autofilter, on a helper col, like this:
Put in say E2: =IF(AND(A2="foo",B2="bar"),"x","")
Copy E2 down to cover the extent of source data
Apply autofilter on col E, choose: x to filter out the required rows
-- 
Max
Singapore

"david.karr" <davidmichaelkarr@gmail.com> wrote in message 
news:22f3d35b-110b-4007-af74-73ad28f483f0@s29g2000yqd.googlegroups.com...
>I have a worksheet with a lot of rows.  I'd like to see all the rows
> in the worksheet that match a particular "query", like "column A =
> 'foo' || column B = 'bar'". How do I do that sort of thing? 


0
Max
5/4/2010 10:47:54 PM
Reply:

Similar Artilces:

Default Row Height through VBA
Is there a way to pick up the default row height for the application using VBA? Thanks in advance MsgBox ActiveSheet.StandardHeight Alan wrote: > > Is there a way to pick up the default row height for the application using > VBA? > Thanks in advance -- Dave Peterson ec35720@msn.com ...

Check DataGridView for existing row
Hello all, I thought this would be simple, I have a DaraGridView with 200 or so rows. All I want to do is check every row to see if a particular string value exists in column[1]. What's the best way to achieve this? I don't really want to do a foreach as there's about 100 + values to compare. Regards, Jon "Jon" <jonmyates@gmail.com> wrote in message news:b6eb3cac-c275-4c07-9360-38fb8a6f8fe2@o10g2000yqa.googlegroups.com... > I thought this would be simple, I have a DaraGridView with 200 or so > rows. All I want to do is check every row to...

how to write a macro, to refer to a last row in a file, in which .
I have the follwing table week B C D E F G H 434 674 453 34 0 13 0 0 435 669 298 29 2 50 3 30 436 673 609 32 8 22 3 90 437 672 872 41 21 2 7 83 438 672 946 54 7 1 5 76 439 660 545 62 19 2 4 74 I need a macro to refer to the last row...that is 6th in this case above. But that will change to the 7th row..or 8th etc. Always in this file I need to refer to the last row. Can we do it in a macro? -raji Raji, There are a number of ways of doing that, the simplest is Dim myLastRow As Range Dim myCell As Range...

IIF function help in query
Hello, I have the following field in the query to look at a field PartDesc, if any of the text have a *BUSH* word, return yes, otherwise no. BUSH: IIF(PartDesc= Like *BUSH*, "yes","no") Why the Like *BUSH* not working? Thanks Because = and LIKE are two operators, a little bit like: 4 * / 3 would mean what (multiply followed by divide)? Probably better to remove the =, in this case: BUSH: IIF(PartDesc Like *BUSH*, "yes","no") Vanderghast, Access MVP "Cam" <Cam@discussions.microsoft.com> wrote in ...

Query criteria 02-02-08
I am looking for a way to format a criteria line to search for a specific character within a group of letters/numbers. Specifically, I am working with 17 digit automotive VIN #'s (Vehicle Identification Numbers) and need to create hits on all VIN's within a table that have a specific character at a specific location within the VIN - 8th character needs to be either a 2, 8 or V. The seven preceding characters are either letters or numbers (in no defined sequence). I know I can use an * after the eighth character as a wildcard. Thanks in advance..... On Sat, 2 Feb 2008 09:47:01 ...

getting list of aliases on Exchange 2007
Hey! One (i think) simple question - how do I get a list of alle the email aliases on and exchange box? - FYI I have the all-in-one-box solution with exchange I can do the get-user cmdlet and get a list of users (by the way - why can't I use the get-mailuser??? It doesn't return ANYTHING - it just goes "plop" and returns to the prompt - why??) But how do a get a list of aliases for a peticular user? I figured that I could do the "pipe" thing between to to functions... Any thoughts?? I need the list (that is going to be loaded into a database) for a custom webpa...

Query 12-27-07
I have a query which gathers a product list from an imported ship history table. To do this I use a group by on [ProdID] and Max on [ShipDate] and [UnitPrice]. My problem is this makes a list where the last Ship Date is obtained and the highest price is listed. Instead I would like it to list the Unit Price associated with the Ship Date (ie. the last shipment price) How would I do this? -- TIA Hi, you need the Max on [ShipDate] without the Max on [UnitPrice]. You might find it easier to get the Max of [ShipDate] then use that to look up the [UnitPrice] for all products for that date...

MATCH and OFFSET with dynamic range
Hello, I would like to do the following with excel Formula. these are the information Sheet 2 A M 01/12/2001 24 01/06/2002 23 01/12/2002 25 01/06/2003 52 01/12/2003 53 there are already 2 range define in sheet 2 rngDate, rgnValues How to obtain something like this with Excel formula and not VBA Sheet1 Jun Dec tot 2003 52 53 105 2002 23 25 48 2001 24 24 I tried to use Match and Offset (but I have a problem with the offset) Any help will be really apreciate :D Ina I noticed that ...

inserting columns within certain rows only
I'm afraid that I know the answer to this but I thought I'd ask anyway ... Is it possible to insert columns for only a certain section of the spreadsheet, i.e., to affect only a certain range of rows? Over the years, I've created a spreadsheet with about 300 rows (and with a 6,000-line macro) and now realize that I need about 10 extra columns ... but only in a certain part of the spreadsheet (rows 100 to 150). If I insert columns, the ENTIRE spreadsheet (from row 1 to 65536) is going to be affected (and all of the address references in my macro will have to be redefined) an...

Trying to have sumif, sum by the row instead of the column
I would like help with the following problem: I want to use the vlookup or sumif fucntion to find Jim and return the total numbers of sales which would be 30 based on the table below. Is there any way to have the sumif fuction sum by rows instead of columns? Name Jan Feb Mar Jim 10 10 10 Thanks for any help Assuming your names are in column A, use an array formula like =SUM(OFFSET(A1,MATCH("Jim",A1:A10,0)-1,0,1,3)) Change the final '3' in the formula to the number of columns you want to sum. Since this ...

Order of Accounts in Account List
I was wondering if there is a work around for the order of accounts in the account list in Money Plus Premium. Specifically I'd like to group some of my credit accounts (i.e. - travel cards, cash back, business cards). Since this doesn't seem to be a standard option (unless I am over looking something) I was wondering about putting a letter before each of the account names to group them. i.e. - "B credit card name" groups all the Business cards, "R credit card name" all the reward cards, etc. Good approach? Does this have some hidden problem I am missi...

Duplicate Queries
I am using Access 2007 query wizards to do the following, but seem to be unable to take it one step further. In the Wizard, I identify 3 columns if they are duplicated to give me the results No problem with this. But I would like to add a fourth column to this query ONLY if this fourth column does not Match itself. Hope I am making this clear enough. Thanks -- Tom On Fri, 15 Jan 2010 08:23:03 -0800, Tom <Tom@discussions.microsoft.com> wrote: >I am using Access 2007 query wizards to do the following, but seem to be >unable to take it one step further. >...

EXPERT HELP REQUIRED
Thanks very much.....all sorted now. I did have some playing around to do but it works, thats the main thin and will be a very useful function in future -- Esson ----------------------------------------------------------------------- Essonc's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1465 View this thread: http://www.excelforum.com/showthread.php?threadid=26273 ...

create list of e-mail addresses from distribution lists
I need to take "Global Address" Distribution Lists and convert them into invidiual e-mail addresses to use for an e-vite. Is there an easy way to do this? "Wanda" <Wanda@discussions.microsoft.com> wrote in message news:E48C11C3-20F8-4B79-8E56-E08FCA13F8A2@microsoft.com... >I need to take "Global Address" Distribution Lists and convert them into > invidiual e-mail addresses to use for an e-vite. Is there an easy way to do > this? Why? Why not send your "e-vite" (whatever that is) to the DL? -- Brian Tillman [MVP-Outloo...

OWA2003 addrbook can't list all entries?
Hi there, I got some 100+ internal email address & groups in exch2003 system. In OWA2003, after launched address book, at "display name" field I press a space & click "find" button, it will list all entries from A to Z. However found that some of the users are not listed. However if I do an explicit search by using the user's firstname or lastname, the entry can be found. Wondering what's wrong with owa2003 address book & how to force update it and make sure it will list every entries from A to Z? Or what is the correct way to list all addr bo...

Re: How to sum up the data which match two criteria?
Hello, What if we use sumproduct =SUMPRODUCT(--(B$1:B$100="Home"),--(C$1:C$100="Mobile"),D$1:D$100) I would like to know the difference in usage of sum(if())) and sumproduct formulas. Regards, Jimmy Joseph ...

about access Control List(ACL)
Hi! I just want to make sure that my understanding about ACL is correct Assume that you have a directory and a file. As I understand this a directory or file can only be connected to one ACL but you can have many Access Control Entries in this ACL. //Tony On 19-04-2010 12:33, Tony Johansson wrote: > I just want to make sure that my understanding about ACL is correct > Assume that you have a directory and a file. > As I understand this a directory or file can only be connected to one ACL > but you can have many Access Control Entries in this ACL. As I read:...

Does nyone have a list of Statement Variables
I am trying to add quantities and unit prices to the items listed under the account activity. -- NateS Hello Nate- I have been attempting to do the same task without much luck, I am currently working with crystal reports to rewrite the AR statement. It is a pain to have to rewrite the report just to add Item quantity and unit price, but MS support tells me the built in AR functions wont do it. Have you had any luck? "nates@sunrisepos.com" wrote: > I am trying to add quantities and unit prices to the items listed under the > account activity. > -- > NateS On ...

Report Parameters forms value list set by VBA?
NOTE: I"m a VBA novice I have a set of reports that use a common Report Parameter form. When I first designed them it was convenient as the same set of filters were applicable to all the reports: Example: report for invoices, report for open orders, report for processing orders Filters were: Location, Manager & Employee Now they also want to filter by date range, which won't consistent between reports: IE. Invoices = 30, 60 or 90 days old vs Pending Orders = 10, 15, 30 days old I'd like to just add one more drop down box to the parameter report and have the value list ch...

BACS reports and listings and comment box
Please could we have the BACS status report and the comment box back on the Print cheques function? The BACS status report was used by many clients as a verification and printed for signature of confirmation that the BACS file was accurate and approved. As there is no other approval on this process and the text file is not encrypted in any way this provided a useful back-up. The comments box on the Cheque Printing window was used to enter a comment referring to when the payment would clear the payees bank account. There is now no feature for being able to include this text as part of...

Combining two rows of information.....
My main purpose is to sort items wihich are used the most often. Right now , hoewever, usage for from. Right now, some items even though they are in the same location(what we're worried about) , are in sepearate rows in this Excel spreadsheet due to the fact that sometimes they're used as an entire box or just as single items. We're not worried about that, what we're worried about is how many tmimes a worker needs to go to this location. Therefore, the two sometimes three rows that have the same location, I'd like to combine. their usage numbers. What would be the best...

blue row numbers
I have blue row numbers and hidden rows that won't unhide but I see no evidence of grouping. There isn't any numbers at the top or anything. I can manually resize the row height but even if I save it afterwards when I go back into it the rows are hid again. Does anyone know why? And how to fix it? Do any of your columns have a dropdown arrow on them? there might be a filter applied. Go into Data>Filter, and if the Autofilter has a tick mark, click it and see if this reveals all the rows. -- HTH RP (remove nothere from the email address if mailing direct) "Grannet"...

InputBox within a query
I have multi records and I want a query where an input box asks for one column record. Column 1 Column 2 Column 3 data place port day plant box I want an input box for Column 1 so if I type day into the box all the data from Column 2 and Column 3 and Column 1 display in a query for me to use in a report and/or form In the criteria for Column1, just type something like: [Please Enter Parameter Value] and the query will prompt for the criteria for Column1 and will only return records that match what the user typed in. But i want to enter the...

Connecting to Online Services- password lengths do not match
When I try to download my online statements from my bank's website using Money 2001, it prompts me for my online password, which is 8 characters long through my bank, but the password box in Money only allows me to input 4 characters. I have tried to change my password through my bank to 4 characters, but the minimum length with them is 8 characters. What should I do? I had the same problem and my bank won't set me up with a four character password. I go to my bank site and log in, then I tell the bank to download to MSMoney. It works for me. Bill "candy4U" <can...

querys from multiple sheets
I have a workbook that conatins 31 sheets, 1 for each day of the month and a report sheet. Each day has information i need to query but i have been unsuccesful at creating a query to pull all the data into a seperate sheet. I'm not to savy on computer lingo so hopefully this makes sense. -- tlachena ------------------------------------------------------------------------ tlachena's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29119 View this thread: http://www.excelforum.com/showthread.php?threadid=488367 It can be done either via formula or VBA. With...