Hi,

this needs to be done in two stages if you don't use VBA. Assume your data
is in A1:B12 with titles on row 1.
1. Create a criteria range: In D1 enter Value and in D2 enter 7
2. Create an output range: in F1 Enter Name. Nothing below it.
3. Run the Advanced filter - choose Data, Filter, Advanced Filter and
choose Copy to another location. Pick A1:B12 as the List Range, D1:D2 as the
Criteria range and F1 as the Copy to range. Check Unique records only and
click OK.

Prepare a second query: Set up the second criteria area
1. In H1 enter Name
2. In H2 enter
=IF(OFFSET($F$1,COLUMN(A1),0)<"","<"&OFFSET($F$1 ,COLUMN(A1),0),"")
3. Highlight H1:H2 and drag the fill handle as far to the right as necessary
- one column for each output from the first step, extra column are ok, so in
this example I dragged to column K. This gave me 4 "Name" and 2 non-empty
criteria.
4. Set up the second output area: Enter Name in a blank column, here I will
use M1
5. Run the second filter: Choose Data, Filter, Advanced Filter, Copy to
another location. Choose A1:B12 for the List range, choose H1:K2 for the
Criteria range, choose M1 for the Copy to range and check Unique records only
and click OK.

Note that I selected a larger criteria that was necessary for the command, I
could have selected H1:I2 just a well. But each column must have Name on the
first row.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"§ Robin Kurian പുതുവത്സരാശംസകള്" wrote:

Hi all,

I am using microsoft excel 2000.
I need help in filtering the data.

I have two columns as below

Name Value
a 1
a 2
a 3
a 7
b 1
b 3
b 9
b 4
c 1
c 3
c 7

I want to find all the distinct Name that doesn't have the given
value.

If we give value as 2.
Then my output will be as follows.

b
c

If we give value as 7.
Then my output will be as follows.

b

How we will define this filter.

Please help me....

Thanx for reading.
Robin