Archive of Mr Excel Message Board
Back to Data in Excel archive index
Back to archive home
Filter questionPosted by Andonny on July 30, 2001 4:09 AM
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
Re: Filter questionPosted 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 questionPosted 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.
Thanks for your help
Re: Filter questionPosted 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.
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.
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):
where 0 stands for blank.
This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store
to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.