Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced filter not in a list
Can I filter a group of items based on a list but return things NOT on the
list. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced filter not in a list
Yes you can. Use an Advanced Filter to do this. There are certain rules you
need to follow to use an advanced filter. 1) You need to position the criteria in cells ABOVE the data list. 2) For multiple criteria on the same field you need to put each one in a separate column This might be easier to explain with an example: A B C 1 Type Sales Sales 2 <Item1 <Item2 3 4 Type Sales 5 Sausages Item1 6 Sausages Item3 Your data range is in cells A4:B6. Use the Data--Filter--Advanced Filter tool, where the list range is your data range (A4:B6) and the criteria range is A1:C2. By placing the not equal to (<) signs in the cells you force excel to filter out those listed and by placing all the criteria on one row you make it an AND statement. The above data would be filtered for those values that were NOT Item1 AND were NOT Item2 (leaving just row 6). Hope that helps. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced filter not in a list
Many thanks for the answer - I don't think I explained myself well enough!
I have two lists - one has a series of values that I know exists in the other but what I want to filter out are the values that are not in both lists. to give you an idea of scale list one is 27000 rows and list 2 is about 4000 rows "Dom_Ciccone" wrote: Yes you can. Use an Advanced Filter to do this. There are certain rules you need to follow to use an advanced filter. 1) You need to position the criteria in cells ABOVE the data list. 2) For multiple criteria on the same field you need to put each one in a separate column This might be easier to explain with an example: A B C 1 Type Sales Sales 2 <Item1 <Item2 3 4 Type Sales 5 Sausages Item1 6 Sausages Item3 Your data range is in cells A4:B6. Use the Data--Filter--Advanced Filter tool, where the list range is your data range (A4:B6) and the criteria range is A1:C2. By placing the not equal to (<) signs in the cells you force excel to filter out those listed and by placing all the criteria on one row you make it an AND statement. The above data would be filtered for those values that were NOT Item1 AND were NOT Item2 (leaving just row 6). Hope that helps. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced filter not in a list
Ahhhh okay, well my previous method won't work for that one!
Only way I can think of to do that is to add an extra column to your larger list, incorporating a VLookUp of the value against the smaller list and then filter on the #N/A errors. "Stuart" wrote: Many thanks for the answer - I don't think I explained myself well enough! I have two lists - one has a series of values that I know exists in the other but what I want to filter out are the values that are not in both lists. to give you an idea of scale list one is 27000 rows and list 2 is about 4000 rows "Dom_Ciccone" wrote: Yes you can. Use an Advanced Filter to do this. There are certain rules you need to follow to use an advanced filter. 1) You need to position the criteria in cells ABOVE the data list. 2) For multiple criteria on the same field you need to put each one in a separate column This might be easier to explain with an example: A B C 1 Type Sales Sales 2 <Item1 <Item2 3 4 Type Sales 5 Sausages Item1 6 Sausages Item3 Your data range is in cells A4:B6. Use the Data--Filter--Advanced Filter tool, where the list range is your data range (A4:B6) and the criteria range is A1:C2. By placing the not equal to (<) signs in the cells you force excel to filter out those listed and by placing all the criteria on one row you make it an AND statement. The above data would be filtered for those values that were NOT Item1 AND were NOT Item2 (leaving just row 6). Hope that helps. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced filter not in a list
The criteria range can be above the data list, but doesn't need to be.
It can be beside the list, or even on a different worksheet. Dom_Ciccone wrote: Yes you can. Use an Advanced Filter to do this. There are certain rules you need to follow to use an advanced filter. 1) You need to position the criteria in cells ABOVE the data list. 2) For multiple criteria on the same field you need to put each one in a separate column This might be easier to explain with an example: A B C 1 Type Sales Sales 2 <Item1 <Item2 3 4 Type Sales 5 Sausages Item1 6 Sausages Item3 Your data range is in cells A4:B6. Use the Data--Filter--Advanced Filter tool, where the list range is your data range (A4:B6) and the criteria range is A1:C2. By placing the not equal to (<) signs in the cells you force excel to filter out those listed and by placing all the criteria on one row you make it an AND statement. The above data would be filtered for those values that were NOT Item1 AND were NOT Item2 (leaving just row 6). Hope that helps. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced filter not in a list
Assuming you have a list of items in a range named MyList
For the criteria area, leave the heading cell blank, and in the cell below, enter a formula that refers to the list of items, and the column in the table that you want to check. For example, if the column to check is column G, and the data starts in row 2: =COUNTIF(MyList,G2)=0 When you run the Advanced Filter, select both the blank heading cell, and the cell with the formula, for the criteria range. Stuart wrote: Can I filter a group of items based on a list but return things NOT on the list. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced filter not in a list
Debra,
I just tried that myself and I can see it works, but can't quite get my head around HOW it works. can you explain it please? DC "Debra Dalgleish" wrote: Assuming you have a list of items in a range named MyList For the criteria area, leave the heading cell blank, and in the cell below, enter a formula that refers to the list of items, and the column in the table that you want to check. For example, if the column to check is column G, and the data starts in row 2: =COUNTIF(MyList,G2)=0 When you run the Advanced Filter, select both the blank heading cell, and the cell with the formula, for the criteria range. Stuart wrote: Can I filter a group of items based on a list but return things NOT on the list. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced filter not in a list
For an Advanced Filter, you can use a formula instead of specific
criteria, if you use a blank heading cell, or a heading that's not in the main table. The COUNTIF formula checks each row's entry in column G, to see if it's in the MyList table. If it's not in the list, the count is zero, so that row passes the filter test, and would be included in the output. There are a few other examples of formulas he http://www.contextures.com/xladvfilter02.html Dom_Ciccone wrote: Debra, I just tried that myself and I can see it works, but can't quite get my head around HOW it works. can you explain it please? DC "Debra Dalgleish" wrote: Assuming you have a list of items in a range named MyList For the criteria area, leave the heading cell blank, and in the cell below, enter a formula that refers to the list of items, and the column in the table that you want to check. For example, if the column to check is column G, and the data starts in row 2: =COUNTIF(MyList,G2)=0 When you run the Advanced Filter, select both the blank heading cell, and the cell with the formula, for the criteria range. Stuart wrote: Can I filter a group of items based on a list but return things NOT on the list. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced filter not in a list
Thanks. Very helpful.
"Debra Dalgleish" wrote: For an Advanced Filter, you can use a formula instead of specific criteria, if you use a blank heading cell, or a heading that's not in the main table. The COUNTIF formula checks each row's entry in column G, to see if it's in the MyList table. If it's not in the list, the count is zero, so that row passes the filter test, and would be included in the output. There are a few other examples of formulas he http://www.contextures.com/xladvfilter02.html Dom_Ciccone wrote: Debra, I just tried that myself and I can see it works, but can't quite get my head around HOW it works. can you explain it please? DC "Debra Dalgleish" wrote: Assuming you have a list of items in a range named MyList For the criteria area, leave the heading cell blank, and in the cell below, enter a formula that refers to the list of items, and the column in the table that you want to check. For example, if the column to check is column G, and the data starts in row 2: =COUNTIF(MyList,G2)=0 When you run the Advanced Filter, select both the blank heading cell, and the cell with the formula, for the criteria range. Stuart wrote: Can I filter a group of items based on a list but return things NOT on the list. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced filter and a list | New Users to Excel | |||
Advanced Filter w/ multiple sheet List Range | Excel Worksheet Functions | |||
Using advanced filter to search for criteria in a list | Excel Discussion (Misc queries) | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions | |||
advanced filter won't allow me to filter on bracketed text (-456.2 | Excel Discussion (Misc queries) |