# Archive of Mr Excel Message Board

Back to Data in Excel archive index
Back to archive home

## Filter question

Posted by Andonny on July 30, 2001 4:09 AM
Hi,
I posted this message before and it showed up on the board and today it's gone :((
I try again:
I would like to filter in culumn A all the unique items I listed in culumn B. I tried advanced Filter without success.

Any ideas how to go about achieving this?

Thanks a lot for your help
Andonny

## Re: Filter question

Posted by Aladin Akyurek on July 30, 2001 4:56 AM
What are you trying to do? Establish whether a unique item of B is in A or not?

=====

## Re: Filter question

Posted by Andonny on July 30, 2001 5:23 AM
I have many items in culumn A (multiple instances) and I would like to list only the once which relate directly to the once in culumn B (some of the items from culumn A but unique not multiple). Like in the example below I would like to filter all items of number 1 and 3 in culumn A. I can do this with autofilter when there are only two but in my case there are at least ten.

Andonny

A
2
1
3
1
2

B
1
3

## Re: Filter question

Posted by Aladin Akyurek on July 30, 2001 6:32 AM
That pesky Advanced Filter... I'll give you what I've done with that. However, I'd like to make another suggestion.
I'll assume your sample data to be in A an B from A5 and B5 on.

(1)

In D5 enter: =IF(COUNTIF(A5:A9,B5)=1,1,0)

Copy down as far as needed. This marks all unique items in B that are also unique in A.
This logic can also be used in Conditional Formatting that you can apply to the data cells of A.

(2)

In A2 enter: =COUNTIF(\$A\$5:\$A\$12,B5)=1

In A4 enter: RecsA [ just a label ]
In B4 enter: RecsB [ just a label ]

List Range: \$A\$4:\$B\$12
Criteria range: \$A\$1:\$A\$2
Copy to: \$F\$4:\$G\$4 [ After checking Copy to another location ]

My worksheet looks like this after using both methods (the area is A1:G9):

{0,0,0,0,0,0,0;FALSE,0,0,0,0,0,0;0,0,0,0,0,0,0;"RecsA","RecsB",0,"Match",0,"RecsA","RecsB";2,1,0,0,0,1,3;1,3,0,1,0,0,0;3,0,0,0,0,0,0;1,0,0,0,0,0,0;2,0,0,0,0,0,0},

where 0 stands for blank.