How do you get an advanced filter to work in Excel, with OR values?

mdershewitz

New Member
Joined
Jul 8, 2013
Messages
11
I have an advanced filter in Excel and I'm trying to set up functionality by which one criteria is equal to one of three values. I see some things online about using ORs with > and <, but not where (x=y) OR (x=z). How do I do this? thanks!
 

mdershewitz

New Member
Joined
Jul 8, 2013
Messages
11
OK, this is very helpful - thanks!

One more scenario though. How do you turn this around in the same general construct to allow for the opposite? In other words, have (x<>y) AND (x<>z). As I have found out, you can't use the OR methodology in the video, because that will turn that into (x<>y) OR (x<>z) and therefore allow for the whole world. I therefore have to create a one-cell operator that has the negative AND.

Thanks!
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,493
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
To use AND, you just repeat the column in the criteria range and put one criterion in one cell and the other in the next (repeat as many times as necessary)
 

mdershewitz

New Member
Joined
Jul 8, 2013
Messages
11
OK. Thanks for the help. I think I finally figured out what I'm trying to do, and none of the answers above 'quite' get you there. I'd love any help to get this Advanced Filter set up!

The logic for the advanced filter would be:

With the positive toggle, (CountryCode = 'France') OR (CountryCode = 'Germany')

With the negative toggle, (CountryCode <> 'France') AND (CountryCode <> 'Germany'). If you put an OR on this line then Germany and France both come up.

I'm having trouble using multiple rows and columns on the filter criteria of Excel to do what I am aiming to do. Thanks!
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,493
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
You haven't really told us what the problem is or what your layout is. Essentially you'd have two columns in the criteria range titled CountryCode and enter <>France under one and <> Germany under the other. That will AND the two.

Generally speaking if you are trying to combine this with other criteria, it may well be easier to use a formula criteria range instead.
 

mdershewitz

New Member
Joined
Jul 8, 2013
Messages
11
Thanks! I'd love to use a formula criteria range...but I haven't read about that yet! Do you have a link you could shoot me on formula criteria ranges?

Thanks
 

Forum statistics

Threads
1,084,753
Messages
5,379,654
Members
401,620
Latest member
Ankur Teotia

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top