Drop 3 Lowest Entries
 

I have a list in B2 thru B11 which includes in this order.... 15 10 48 16 27
8 2 21 10 60

What I woujld like to do is highlight the range and have the 3 lowest numbers
deleted, but I dont want them sorted first, the remaining 7 must stay in the
original cells. Here's where it gets tricky, because 10 is one of the lowest 3
and there are 2 10's, I want to keep one of the 10's (it doesn't matter which
one). So after all is done I would be left with 15 48 16 27 21 10 60

TIA, Dennis
================

Hi Dennis,

Try this

Sub test()
Dim rng As Range
Dim iRow As Long
Dim i As Long

Set rng = Selection
For i = 1 To 3
iRow = Application.Match(Application.Small(rng, 1), rng, 0)
Cells(iRow, rng.Column).EntireRow.Delete
Next i

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dennis" wrote in message
m...
I have a list in B2 thru B11 which includes in this order.... 15 10 48 16
27
8 2 21 10 60

What I woujld like to do is highlight the range and have the 3 lowest
numbers
deleted, but I dont want them sorted first, the remaining 7 must stay in
the
original cells. Here's where it gets tricky, because 10 is one of the
lowest 3
and there are 2 10's, I want to keep one of the 10's (it doesn't matter
which
one). So after all is done I would be left with 15 48 16 27 21 10
60

TIA, Dennis
================

Try this in C2 and fill down:

=IF(AND(COUNTIF($B$2:B2,B2)<=1,OR(B2=SMALL($B$2:$B $11,
{1,2,3}))),"",B2)

HTH
Jason
Atlanta, GA

-----Original Message-----
I have a list in B2 thru B11 which includes in this
order.... 15 10 48 16 27
8 2 21 10 60

What I woujld like to do is highlight the range and have
the 3 lowest numbers
deleted, but I dont want them sorted first, the
remaining 7 must stay in the
original cells. Here's where it gets tricky, because 10
is one of the lowest 3
and there are 2 10's, I want to keep one of the 10's (it
doesn't matter which
one). So after all is done I would be left with 15 48
16 27 21 10 60

TIA, Dennis
================
.

Thanx Bob!! That works great for for getting rid of the three lowest entries.
What I need it to do tho is just delete the 3 entries out of the cells they
are in and leave all the rows intact. This is a great start for me tho, I'm
thinking I might need helper cells?

Thanx for the quick response Bob!!!

Dennis
=============


In article , "Bob Phillips"
wrote:
Hi Dennis,

Try this

Sub test()
Dim rng As Range
Dim iRow As Long
Dim i As Long

Set rng = Selection
For i = 1 To 3
iRow = Application.Match(Application.Small(rng, 1), rng, 0)
Cells(iRow, rng.Column).EntireRow.Delete
Next i

End Sub

Hi Dennis,

How about this then, no helper cells

Sub test()
Dim rng As Range
Dim iRow As Long
Dim i As Long

Set rng = Selection
For i = 1 To 3
iRow = Application.Match(Application.Small(rng, 1), rng, 0)
Cells(iRow, rng.Column).Delete Shift:=xlUp
Next i

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dennis" wrote in message
m...
Thanx Bob!! That works great for for getting rid of the three lowest
entries.
What I need it to do tho is just delete the 3 entries out of the cells
they
are in and leave all the rows intact. This is a great start for me tho,
I'm
thinking I might need helper cells?

Thanx for the quick response Bob!!!

Dennis
=============


In article , "Bob Phillips"
wrote:
Hi Dennis,

Try this

Sub test()
Dim rng As Range
Dim iRow As Long
Dim i As Long

Set rng = Selection
For i = 1 To 3
iRow = Application.Match(Application.Small(rng, 1), rng, 0)
Cells(iRow, rng.Column).EntireRow.Delete
Next i

End Sub

Thanx again Bob, works great!!!!!! Man you guys are good, and fast!

Dennis
==================



wrote:
Hi Dennis,

How about this then, no helper cells

Sub test()
Dim rng As Range
Dim iRow As Long
Dim i As Long

Set rng = Selection
For i = 1 To 3
iRow = Application.Match(Application.Small(rng, 1), rng, 0)
Cells(iRow, rng.Column).Delete Shift:=xlUp
Next i

End Sub