Archive of Mr Excel Message Board

Back to Forms in Excel VBA archive index
Back to archive home

A "reverse" filter question

Posted by BToff on September 13, 2000 8:01 PM

Is there a way to filter out an instance of a row having no duplicates within the set of rows?
i.e., 1000 rows. 996 rows have at least one other same value within a column. 4 rows have no duplicate values in that column. Can I filter out those 4 rows?

Column1 Column2 Column3
ABC DEF 123
ABC DEF 345
BCD GTH 378
DEF QED 767
GHY QED 478

I'd like to filter out the row that has GTH in Column2.

Check out our Excel Resources

Re: even better

Posted by Tim Francis-Wright on September 14, 0100 2:42 PM

I've assumed that Column 1, Column 2, and Column 3
are your headings in columns A, B, and C.

Set up Column D with D1 = "Column 4"
and D2 to the end of the list being
=COUNTIF($B$2:$B$65535,$B2)=1

This sets a cell in D to be TRUE if it appears
once and only once in Column B.

Then, you can use the AutoFilter menu item
to filter out the offending rows (and use
the SUBTOTAL function to add them and the like).

HTH

Works like a charm. Thanks!

Posted by BToff on September 14, 0100 3:47 PM

even better

Posted by BToff on September 13, 0100 8:08 PM

THY LMN 487 I'd like to filter out the row that has GTH in Column2.
And the row with LMN in Column2., i.e., 2 unique cells with no duplicates in Column2.

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.