filtering question

R

Rafi Kfir

Hi,
I would like to explain my question in an example:

Table
a 50
a 56
b 45
b 23

As a result of the fileter I would like to see only one line of the
parameter appears in the first column:

wanted result:
a 50
b 45

Can you show me how to do it?

Thanks
Rafi
 
S

Stefan Hägglund [MSFT]

Hi Rafi!

You can use Autofilter for this.

If you have the data in column A and B:
Activate one cell in the datarange, then select Autofilter from Data,
Filter.
Now you get a blue arrow in all cells of the datarange in the first row.
Click on the blue arrow in column B
Select Custom from the menu
Set the two criterias to:
Equals = 50
or
Equals = 45
Then click OK

Now the filtered results is:
Col A Col B
a 50
b 45



Best regards

Stefan Hägglund
Microsoft
 
D

Debra Dalgleish

If you want to filter for the first record for each code in the table,
you could use an Advanced Filter. There are instructions here:

http://www.contextures.com/xladvfilter01.html

In the criteria area, leave the heading cell blank. In the cell below,
enter the formula, changing the references to match your table:
=COUNTIF($A$2:A2,A2)=1
 
R

Rafi Kfir

Thank you very much Debra,

I read (although not in full) the attached link and tried to use the
suggested fomula. Unfortunately, not only that it does not work, but
I also get an error when I try to use it in the creteria range.

Let me assign the cell for this example and please give me an exact
fomula.
(the names are only for clerification. The real table is much longer
and with different names...)

(A2) Name (B2) Sum
(A3) abc (B3 34
(A4) abc (B4) 12
(A5) xyz (B5) 33
(A6) xyz (B6) 77
(A7) MMO (B7) 45
(A8) MMO (B8) 22

The desired result after the fomula is:

(A2) Name (B2) Sum
(A3) abc (B3 34
(A4) xyz (B4) 33
(A5) MMO (B5) 45

(D2) Criteria
(D3) =COUNTIF()=1 ???? , I get FALSE!!!?

Under the Advanced Filter I'll put:

List range: $A$2:$B$8
Criteria range: ???

shall I put the formula (I get "illigal...") , or the cell with the
fomula? (but I can't put the formula since I get FALSE.)

Please excuse my ignorance, but I need a detailed answer.

Thank you
Rafi.
 
Top