Advanced filter not in a list
 

Can I filter a group of items based on a list but return things NOT on the
list.

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.

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.

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

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

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.

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

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

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