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.

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

Re: even better

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

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

Works like a charm. Thanks!

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

even better

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.