The Golf News Feed
Question by unpronounciable: How to arrange 1 column in Excel according to values in another column (USING FORMULA)?
First, I give an example. This is the result from 2010 Masters (golf).
COLUMN A ••••• COLUMN B
Fred Couples •••••••• -9
K. J. Choi •••••••••••• -11
Tiger Woods ••••••• -11
Phil Mickelson ••••• -16
Lee Westwood ••••• -13
Anthony Kim •••••••• -12
Column A is name of player, and column B is their score.
Now I want to arrange in Column D, the name of players in ascending order of their score (from lowest to highest), and Column E will be their respective scores.
(Column C will be number 1, 2, 3 and so on.)
[The reason I want to use formula is because I don't want to keep arranging them using the 'Sorting' feature, although I know this can be done. I want the system to do itself.]
I noticed, LOOKUP formula cannot be used, since it requires the data to be already in ascending order, so I used INDEX and MATCH.
=INDEX(A:B,MATCH(SMALL(B:B,C1),B:B,0), 1)
This is the sequence of the computation:
C1 = 1 [C2 = 2, C3 = 3 and so on]
SMALL(B:B,C1) = SMALL(B:B,1) = -16 [This will find the smallest value in column B.]
MATCH(-16,B:B,0) = 4 [It will find the value "-16" in Column B, which is the 4th Row]
INDEX(A:B,4,1) [It will return the value in the 4th Row of 1st Column]
So the formula will yield “Phil Mickelson”.
(Column E formula is SMALL(B:B,C1), where the calculation steps are the same as above.)
If the formula continues downwards, it will return “Lee Westwood” as 2nd and “Anthony Kim” 3rd.
HOWEVER, the problem comes at the 4th. Both “K.J. Choi” and “Tiger Woods finished 4th with the same score of -11. If the same formula is used, the name “K.J. Choi” will be displayed twice.
THIS IS THE QUESTION:
How can the formula be modified to make the 5th Row displaying “Tiger Woods”, although the score is the same as the previous row?
Best answer:
Answer by Andrew L
Hi
Unfortunately, Excel cannot distinguish between two equal values, the only way you can do this is with a Sort.
The only other way to do this is to add a tiny fraction to one of the identical scores.
-11.0001. Format the cells so no decimal appears.
This will produce the correct result with SMALL(B:B,ROW()-1) dragged down column D.
Add your own answer in the comments!
If you enjoyed this article, please consider sharing it!
Put this array formula in D1:D10,
=INDEX(A:A, 100*MOD(SMALL(B1:B60+(ROW(B1:B60)/100), ROW(Z1:Z100)), 1), 1)
It will need to be entered with Ctrl-Shift-Enter (Cmd+Return for Mac)
B1:B60 + (ROW(B1:B60)/100
will return an array of numbers with the score on the left of the decimal place and the row number on the right so Tiger Woods gets -11 + .003 = -10.997 and Choi gets -11 + .002 = -10.998. Thus diferentiating between the two. There are no duplicates in this array.
The SMALL then sorts those values, 100*MOD(SMALL(..)) turns those into row numbers and INDEX will return the player’s names.
One Problem is that empty rows will be sorted as a player who scored 0. Adding IF will take care of that.
=INDEX(A:A, 100*MOD(SMALL(IF(A1:A60<>“”, B1:B60+ROW(B1:B60)/100) ,ROW(Z1:Z100)), 1),1)
entered with Ctrl-Shift-Enter (Cmd+Return for Mac)