Filter in Microsoft Excel

[§ Robin Kurian à´ªàµà´¤àµà´µà´¤àµà´¸à´°à´¾à´¶à´‚à]

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

 

[Shane Devenshire]

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, D12 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.

 

[Ashish Mathur]

Hi,

Assume that the data is in range B3:C14. Now in cell D17, enter 2. In B16
enter Name and in cell C16 enter Condition. In cell C17, enter the
following formula

=COUNTIF($B$4:$B$14,B4)=SUMPRODUCT(($B$4:$B$14=$B4)*($C$4:$C$14<>$D$17))

Now perform the following steps:

1. Go to Data > Filter > Advanced Filter;
2. Click the radio button for Copy to Another location
3. In the list range, give the range reference as B3:B14
4. In the criteria range, give the reference as B16:C17
5. In the copy to box, give the reference of any blank cell
6. Check the box for Unique records only
7. Now click on OK.

Please remember that this is not a dynamic solution I.e. when you change the
value in cell D17 to 7, you will have to rerun the advanced filter.

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

message
news:[email protected]...