Highest value in column b returning column a
 

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

Highest value in column b returning column a
 

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

Highest value in column b returning column a
 

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

Highest value in column b returning column a
 

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