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
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 http://geocities.com/aaronblood/Menu.html).
Note. The above array-formula does not require you to sort your data.
Aladin
