# 3 of a kind, rest delete

Posted by Andonny on February 13, 2001 1:51 AM

Hi,
I am seeking a solution to the following:

I have usually 3 of a kind in culumn B. The odd occasion I have 2 of a kind or even only one of a kind. I need to have the rows of 3 of a kind only and have all other rows deleted.

In the example below it should delete rows with kiwi and the rows with banana because there are not 3 of a kind.

Example:
......A......B.......C
1....24.....apple....500
2....10.....apple....400
3....23.....apple....200
4....12.....banana...11
5....13.....banana...33
6....25.....pear.....5
7....16.....pear.....6
8.....55....pear.....10
9....34.....kiwi.....45

Thanks a lot for your help
Andonny

Posted by Mark W. on February 13, 2001 7:16 AM

Insert a new row above row 1:1. Enter the
formula, =COUNTIF(B:B,B1), into cell D2 and
copy down. Next, apply a Custom AutoFilter
to column D using "does not equal" 3 as the
criteria. Delete the displayed rows (those
with a row number colored in blue). Remove
the AutoFilter.

Posted by Dave Hawley on February 13, 2001 7:22 AM

Hi Andonny

Try this.
Select a cell in your table and go to Data>Subtotals.

Set to Count at each change in your fruit Column .Click Ok

Set Subtotals to level two, so you only have the subtotal results showing.

Sort by The subtotal column, Ascending.

delete all rows with values less than 3.

Remove Subtotals.

Hope this helps