S
Stuart
Can I filter a group of items based on a list but return things NOT on the
list.
list.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
Dom_Ciccone
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.
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.
S
Stuart
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
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
D
Debra Dalgleish
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.
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.
D
Debra Dalgleish
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.
It can be beside the list, or even on a different worksheet.
D
Dom_Ciccone
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.
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.
D
Dom_Ciccone
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
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
D
Debra Dalgleish
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 here:
http://www.contextures.com/xladvfilter02.html
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 here:
http://www.contextures.com/xladvfilter02.html
D
Dom_Ciccone
Thanks. Very helpful.
Debra Dalgleish said: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 here:
http://www.contextures.com/xladvfilter02.html