Filter two columns with criterion applying to one or the other?

Hi,

I am looking for a solution to the following filtering problem:
I have two adjacent columns, so using a filter for both of them is no
problem. But what I want to do and don't know how to do is this:
I want to filter for values greater than x (a certain number, in my
case 5000) in any of the two columns. I can filter both columns for x
greater than 5000 but that filters out more than I want because there
may be some cells with a value greater than 5000 in only one of the
two columns.

Is there a solution to this problem (using Excel alone or an add-on)?

Peter
0
1/26/2005 3:42:09 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
572 Views

Similar Articles

[PageSpeed] 57

Hi Peter

you can use the advanced filter to do this ... insert 4 blank rows above 
your data range and copy the headings of your data to the first of these 
blank rows, in the first column that you want to filter on, under the 
heading type
>5000
now click on the next line down (ie row 3) under the second column that you 
want to filter on and type
>5000

now click back in your data area and choose data / filter /advanced filter
the list range should be automatically selected for you
click in the criteria range line and then select the heading range and the 
two lines with the criteria in them (e.g. A1:D3)

click the OK button.

Hope this helps
Cheers
JulieD

"Peter Frank" <peter_frankde@yahoo.de> wrote in message 
news:8af8ecf.0501260742.1080e92d@posting.google.com...
> Hi,
>
> I am looking for a solution to the following filtering problem:
> I have two adjacent columns, so using a filter for both of them is no
> problem. But what I want to do and don't know how to do is this:
> I want to filter for values greater than x (a certain number, in my
> case 5000) in any of the two columns. I can filter both columns for x
> greater than 5000 but that filters out more than I want because there
> may be some cells with a value greater than 5000 in only one of the
> two columns.
>
> Is there a solution to this problem (using Excel alone or an add-on)?
>
> Peter 


0
JulieD1 (2295)
1/26/2005 3:54:48 PM
Reply:

Similar Artilces:

columns in RMSSO
Hi There, Is it possible to change the columns on the forms ?? For example: Receiving po contains: Type, Item Lookup Code, Reorder Number, Description, OtyOrd, QtyRTD, QtyRcv, Cost, Price, NewPrice, Extended We need: Item Lookup Code, Description, MPQ, Price, BinLocation We need to modify few other forms as well. I think I've seen that in one of the add-ons while ago, but I cant remember which one was it and I'm not 100% sure if I really did. Thanks, Arthur ...

Intelligent Message Filter- can the junk email feature be turned o
I am wondering if there is a reg hack or other method of running the Intelligent Message Filter but disabling the feature to send stuff to junk email? I have temporarily uninstalled it for customer since there were too many complaints about stuff going into to junk email. The filter capacity for the most part works pretty well and what is going into archieve looks like SPAM with only minor exceptions. I was looking at reinstalling the Intelligent Message Filter and not setting the second setting –Store Junk Email Configuration. Although I intially had the settings SCL lower I raised...

Getting appts to print on one page
I am using calendar assistant to print multiple overlaid calendars at once on letter size paper. All the appointments show up on the first page, except for one. This one pushes to a second monthly page duplicating the first. ie - I get two November 2009s - one with all appts except one and the other page with only the one rogue appointment -- Thanks, Alison ...

Junk email filter
I did an upgrade from 2002 office. The Junk email filter works great only on the Inbox. I check three different eamil accounts and have rules to move the messages to their respective folders that I created for each email account. The Junk email filter does not work on these folders. the rules are as follows; Apply this rule after the message arrives through the "Specified" account, move it to the "Specified" folder. Thanks Tom Check the order of your filters. (tools-rules wizard). Move the rules up or down to satisfy your needs. If your rules to move to specifi...

Completely Turning off Junk Email Filtering in Outlook 2003
Does anyone know how to completely turn off Junk Email filtering Outlook 2003? We want all emails, regardless if spam, to go directly to every users' Inbox. Thanks. That's a first, but from the end user standpoint Tools | Options | Preferences | Junk E-mail. Only other way I can think of is when the profile is generated (assuming CIW is in use) and Exchange is in use, is to turn off cached mode. "Chris H" <ChrisH@discussions.microsoft.com> wrote in message news:C250A388-DEFA-4402-8449-80FF3FC91B4C@microsoft.com... > Does anyone know how to completely turn o...

anyway to make outlook to execute some vbs or js script for complex filtering
or would I have to make use of .net? at this place there is no .net implemented Maybe the outlook vba group might be a better place to post "MSDN newsgroup" <NoOne@allMailSunken.net> wrote in message news:%23Mp53SYEKHA.4184@TK2MSFTNGP02.phx.gbl... > or would I have to make use of .net? > > at this place there is no .net implemented > "MSDN newsgroup" <NoOne@allMailSunken.net> wrote in message news:%23Mp53SYEKHA.4184@TK2MSFTNGP02.phx.gbl... > or would I have to make use of .net? > > at this place there is no .net implemented mic...

Reference column question
Setting up a tabulation speadsheet,has 40 rows and 10 columns.Question is when I do my formula which is a subtraction for each column,I want to use a refernce # from the first row of each column =Sum(F1-D3)=SUM (G1-D3)and so to=Sum(P1-D3)Rather than enter each manually,because cut and paste adds 1 to each column how can I get to use f1 as reference for that column G1 for that column.Thanx If I understand correctly, try putting a $ in front of the row reference. Also, sum is not necessary for what you are doing. =F$1-D3 as you copy that down it becomes =F$1-D4 copied across to the ri...

how to add a resource from a different project in to existing one
I created one project with so many dialogs. But now I hav eto go for another project as there is change in userinterface but much of the dialogs will be useful to me. Is there any short cut so that I can use that project resource in to my project. I am in VS 8.0 Pls guide. Thanks If the dialogs can be reused in both projects, you could wrap these in a dll (resource only or a regular MFC dll). Another option would be to cut paste the resources in resource editor. --- Ajay Bhargs wrote: > I created one project with so many dialogs. > But now I hav eto go for another project as there is...

Garnish Codes not "talking" to one another when setting max deduct
We have a couple employee who have more than one garnishment coming out of their paycheck. The total of these garnishments cannot be greater than 50% of net disposable income (gross pay minus required withholding taxes) by state law. However when using more than one garnishment code, they don't "talk" to one another such that the total of the two garnishment stops at 50%. So a person may be garnished more than 50% of their disposable income. I see at least one other person has had this problem; is anyone aware of an efficient work-around and/or a way to do it in the sys...

SumIF using two criteria
I have a sumif question. I have several columns of data. I want to sum one column based on another column's criteria but only to want to sum the data of the first column if it is above a certain score. This is formula i am using so far =SUMIF(F4:F253,">207",D4:D253). I am trying to sum range 'F' if it is greater than 207 but only if the corresponding number in range 'D' is 3. I have tried using several different methods but this one gets me the closest. I am trying to simplify my equations alread in place so i do not have to reenter the row numbers...

Filtering, Counting and Denying
Sounds like a strange subject name, but here's what I want to do. I have a table called BOOKINGS. In it are all the daily car bookings - past, present and future. Each booking is identified by DEPARTMENT (1=DA, 2=Finance, 3=Exec, etc), and BOOKINGDATE. I want to be able to deny any department - other than 1-DA - the opportunity to book out one of our vehicles if the total external department count of bookings exceeds two on any given day. Not sure how to go about this, but I think I need to filter out the daily bookings by date, then count all records that are not department 1, and t...

why can't I edit a document I created two days ago with same Word
Started using Windows 7 about two weeks ago. Now I can't edit files I just created within the last week using the Word 2007. Why? Hi, I am really sorry that you are experiencing that. I am not quite sure yet thatI fully understand your problem but from what I see it seems taht you are trying to modify a read only file. Make sure taht when you save in word you are not saving the document as "read only." Let me know if this helps and if you have any additional info I would be glad to help you . -- Vicente Tulliano "veteranpoet" wrote: > ...

All Columns are not to move
How can I stop the columns from moving to the left of the Table? -- Roger On Sun, 3 Feb 2008 17:28:39 -0800, Roger <Roger@discussions.microsoft.com> wrote: >How can I stop the columns from moving to the left of the Table? You'll have to explain what on Earth you're talking about, Roger. What columns are "moving"? How? John W. Vinson [MVP] Good evening John, I found my own answer:by bringing the cursor to the first cell, the first column will not scroll to the left anymore; however, when I bring the cursor to any other cell and scroll to the rig...

multiple fies updating one file
Hi, How would I setup an excel sheet that needs to collect numbers from other excel sheets. The rows and fields numbers will keep growing. I know how to reference a cell in a file but not sure how you can quickly create a reference to a range of cells and how about when the range keeps growing. Thank you Hi Vic; You can select and copy the range you want to link, then go to then go to the receiving sheet and click the top right cell. Click Edit / Paste Special and select Paste Link. This will link the entire range. If you happen to be running Office 2007 you can right click into the ...

Text-To-Columns Fixed Width
When using Text-To-Columns, Excel "recognizes" that the data fits the Fixed Width criteria and PRE-assigns the width. In most of the cases I work with Excel is wrong 99% of the time. Is there a way to force Excel to NOT pre-assign the width (leave everything blank)? On the first panel of the wizard click Delimited, then on the second panel click space as the delimiter. Hope this helps. Pete On Feb 4, 12:50=A0am, TP <T...@discussions.microsoft.com> wrote: > When using Text-To-Columns, Excel "recognizes" that the data fits the Fix= ed > Width ...

How to get total "conditional sum of cells" in a column?
Hi all, I have dollar amounts in one col, and status in another. I want the sum of those dollar amounts where the corresponding status cell is empty (blank). How do I do this? Thanks for any hints, cdj Status in Column A and dollar amounts in Column B: =SUMPRODUCT((A2:A100="")*B2:B100) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk "sherifffruitfly" <sherifffruitfly@gmail.com> wrote in message news:bc08584b-1338-4b3f-8ab3-cc3e1602e581@n1g2000prb.go...

Sort column with first and last name by last name
Hello, I have a mailing list with 10,000 names. The first cell has first an last name in the same cell. First name is listed first. Is there formula to sort and/or separate text in a cell. I would like to sort o separate the first name from the last in the cell to allow for a mai merge by name. Using Excel 2002. Thank you -- Message posted from http://www.ExcelForum.com If you have just first and last names separated by a space, Data>Text to Columns would be the easiest method to split into two columns. If more than that, like names with van or von or de etc. you may need a different m...

Outlook Synch error
I have one user and only one user have trouble going back online with the Outlook client. The user is connected to our VPN and then when the user clicks Go Online, it starts to synch then she gets the "the logged-on user does not have the appropriate security permissions to view the records or peform this specifc action" error/message. Outlook works fine and the user can use web CRM just fine. I've already tried changed the user's IE security settings to low, didn't help. Any ideas? James James, Could you provide a bit more information about the user, specifica...

Move OL 2007 and all files to another computer.One on XP one on Vi
Is there a detailed "How Do I file" covering moving OL 2007 with BCM from one computer (that has a damaged or correpted C: Drive ) to another or new computer. I have have some very helpful advice from an earlier post a few adys ago but if a How do does exist that would be very helpful. Thanks in advance. Andy To backup and restore your Outlook data see; http://www.howto-outlook.com/howto/backupandrestore.htm For backing up and restoring BCM data see; http://office.microsoft.com/en-us/outlook/HP011134881033.aspx -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsof...

Filter suddenly limited
I have an excell file that is about 2000 rows. I use the filter t locate rows with same names, The filter now only works thru row 1256. Any suggestions what is causing this -- Neil Hanawal ----------------------------------------------------------------------- Neil Hanawalt's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1589 View this thread: http://www.excelforum.com/showthread.php?threadid=27380 Hi see: http://www.contextures.com/xlautofilter02.html#Limits -- Regards Frank Kabel Frankfurt, Germany Neil Hanawalt wrote: > I have an excell file that is a...

Finding unique numbers in a column
Is there any way to find the number of unique values among a set o values in a column in an excel sheet. I would also like to know th number of times each value appears in the column. Thank -- coolkid39 ----------------------------------------------------------------------- coolkid397's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2431 View this thread: http://www.excelforum.com/showthread.php?threadid=37924 There was also a similar thread under Excel Worksheet functions titled "Delete duplicates". Solution provided was to use: Data>Filter&g...

Printing a two spreadsheets front and back
I have clicked select all sheets and selected two sided copy in print options. It gives me a message that 1 page has been printed, when I hit print, but when I go to the printer, there are two sheets of paper. Each spreadsheet on its own piece of paper. i have done this before, but when I do I print several copies, and months go by before I have to print the two sheets front and back again. Help. hi technically all excel does is send the data. seems to me the problem may be in your printer, not excel. make sure that you are printing to a duplex printer. regards FSt1 ...

move page from one document to another
I need some "fast" help! I have never worked in Publisher before, but need to get a project done for an organization I work for. I need to take several pages from one document and paste them into another document. Both documents are already set up in Publisher. I want to basically "copy" from one document to another. HELP!! kdramirez <kdramirez@discussions.microsoft.com> wrote: >I need to take several pages from one document and paste them into another >document. Both documents are already set up in Publisher. I want to >basically "copy&qu...

Multiple emails w/ same tracking token in one activity?
Is it possible to track an ongoing email conversation as a single activity (assuming tracking token is present and accurate), instead of having each outgoing and incoming email result in a separate activity? One troubleshooting conversation with a contact can end up creating 12 activities in their history. If anyone knows the answer and/or can tell me how to do so, please let me know. Thanks! Afraid not. Each of those seperate emails is a seperate "activity" and thus is tracked seperately. -- Matt Parks MVP - Microsoft CRM "SCAB" <SCAB@discussions.microso...

Mutiple Table Entry into One form Wthout using subforms
Hello all I have created a form by adding information from several differnt forms and table as they areall getting input by several differnt people on the network. the problem i am having is that i can not make this new form add records and save them for later query or report from it? could someone please help -- Message posted via http://www.accessmonster.com Why do you not want to use subforms? Unless you can join the tables in such a way that the resultant query is updatable (usually a challenge), subforms are the easiest approach. -- Doug Steele, Microsoft Access MVP http://I.Am/D...