thanks for taking time to respond. I ended up recording a macro then
inserting buttons to to run the macs.

"Martin" wrote:

Deceptively difficult problem. I've been meaning to develop a function that
returns a range excluding certain defined cells so I've used this as the
excuse - apologies if this isn't the place for a macro or user defined
function (udf).

First of all, copy and paste (all of!) this NewRange function into a new
module in your file (go to Tools, Macro, VB Editor and then Insert, Module):

Function NewRange(OldRange As Range, ParamArray ExcludedCells()) As Range
Application.Volatile
Dim myCell As Range
Dim vnt As Variant
Dim myRange As Range
Dim myCells(10000) As String
Dim str As String
Dim n As Long
Dim m As Long
m = 0
Dim myFlag As Boolean
myFlag = False
For Each myCell In OldRange
For n = 0 To UBound(ExcludedCells())
If IsArray(ExcludedCells(n)) Then
For Each vnt In ExcludedCells(n)
If myCell.Value = vnt.Value Then
myFlag = True
End If
Next vnt
Else
If myCell.Value = ExcludedCells(n).Value Then
myFlag = True
End If
End If
Next n
If Not myFlag Then
myCells(m) = myCell.Address
m = m + 1
End If
myFlag = False
Next myCell
str = myCells(0)
For n = 1 To m - 1
str = str & "," & myCells(n)
Next n
Set NewRange = Range(str)
End Function

Secondly, reverse the order of columns A and B so we can use a Vlookup later
(easiest done by dragging the edge of the selected column B with the Shift
key down).

Thirdly, type the following formulas in J7, K7, etc:
=MAX(A1:A6) =MAX(NEWRANGE(A1:A6,J7)) =MAX(NEWRANGE(A1:A6,J7:K7)) etc

Finally, type the following Vlookup in J8 and copy it across:
=VLOOKUP(J7,$A$1:$B$6,2,FALSE)

However, I think you'd be better off just sorting the columns!!!


"macamarr" wrote:

What I have is two columns Column A is the Emp ID column B is "number of
times selected" What I am attempting is to list the top five Emp ID based on
the number of times they were selected.The top five wuold list in different
cells off to the side.
This gets updated daily and is ever changing. Instead of having to sort
everytime additions are made I am trying to make it a bit easier. Thanks for
you help!

EXAMPLE:
A B J7 K7 L7 M7 N7
1 66
2 69 5 3 4 2 1
3 74
4 71
5 79
6 68