Highest value in column b returning column a

[macamarr]

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

[chillihawk]

There's a couple of issues here, firstly the formulas:

In column C put the formula =rank(B1,B1:B6)

In the top 5 cells (K7:N7) put the formula
=index(A1:A6,match(n,C1:C6,0),1)
where 'n' is 1 to 5.

Second issue is how do you make it simple if you add or remove
employees? You could structure your sheet in such a way that there is
nothing else in Columns A, B, C and extend the ranges in the formulas
(B1:B6 and C1:C6) a long way down the sheet. Or you could name the
ranges in your formulas and when you needed to add or remove an
employee you would just need to make sure you inserted or deleted while
preserving the range. I guess it depends on how many times you would
be doing it. HTH

[Martin]

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
>
>
>
>
>
>

[macamarr]

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
> >
> >
> >
> >
> >
> >