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

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?

Aladin

=====

Posted by Andonny on July 30, 2001 5:23 AM

Hi Aladin,
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.

Thanks for your help
Andonny

A
2
1
3
1
2

B
1
3

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 ]

Activate A5, activate Adv Filter.

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.

Aladin

=============