MrExcel Publishing
Your One Stop for Excel Tips & Solutions

3 of a kind, rest delete

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

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.


Thanks a lot for your help

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

OzGrid Business Applications

Posted by Aladin Akyurek on February 13, 2001 7:33 AM

Array enter

D1 =SUM(1*($B$1:$B$9=B1)) [ copy down as far as needed ]

The values in D will indicate whether a row belongs to a triplet or not.
I assume that you literally want to remove the rows which does not contain a 3 in D. If this is true, you can write VBA-code that will delete rows by looking at the value in D, or you can adapt the VBA-code of Aaron Blood which is to be found in his FindDelete_VBA.xls to this task (see

Note. The above array-formula does not require you to sort your data.