VBA or Excel Function to sort

[birkato]

Hi, I have a set of data. Lets say there are 10 people that answer and bunch of questions and based on the questions, there is a formula that calculates 10 separate "scores" that describe the person. So for each person there are 10 "scores" (say Score A through Score J) that range from 0 to 100.

What I need to do is to rank them as 1st, 2nd...10th and to identify the score type for each one. I currently am doing this with a formula using the LARGE function (no VBA). This works great but the only catch is that some of the scores are identical ties. Say for instance I have the following scores:
Score A = 100
Score B = 90
Score C = 80
Score D = 80
Score E = 70
Score F = 60
Score G = 50
Score H = 40
Score I = 30
Score J = 20

My LARGE function currently produce the following ranks....1st = Score A, 2nd Score B, 3rd = Score C, 4th = Score C, 5th = Score E....(it skips Score D). I can understand why as this function probably doesn't have any way of breaking a tie. I realize this functionality might require a VBA script. I am OK with VBA, but does anyone have any pointers on how to start? I was thinking I would read in all 10 scores for a user, set a DO UNTIL loop with an index to run 10 times, and cycle through finding the highest to the lowest. I just don't know how to exclude or deal with the case when there is a tie. How do I tell it to accept one as the first tied rank and then accept the second tied score as the other rank? Not looking for someone to code this for me, I just am looking for some tips to get started. Would it be easier to literally tell excel to write the scores along with the score types, then issue a command to re-sort the data and then read it back in? This would be simple to do by hand if there were a few users but I have thousands of users.

 

[jorismoerings]

Hi,

Before taking refuge to VBA;
try this:

Book1
	A	B	C
1	Person 1	Score A = 100	1
2	Person 1	Score B = 90	2
3	Person 1	Score C = 80	3
4	Person 1	Score D = 80	4
5	Person 1	Score E = 70	5
6	Person 1	Score F = 60	6
7	Person 1	Score G = 50	7
8	Person 1	Score H = 40	8
9	Person 1	Score I = 30	9
10	Person 1	Score J = 20	10
11	Person 2	Score J = 20	10
12	Person 2	Score G = 50	7
13	Person 2	Score H = 40	8
14	Person 2	Score C = 80	3
15	Person 2	Score D = 80	4
16	Person 2	Score F = 60	6
17	Person 2	Score I = 30	9
18	Person 2	Score E = 70	5
19	Person 2	Score A = 100	1
20	Person 2	Score B = 90	2
Sheet1

Cell Formulas
Range	Formula
C1	=COUNTIFS(B$1:B$20,"<"&B1,$A$1:$A$20,A1)+1

 

[birkato]

Hi: Thanks for this suggestion and I see what the formula is trying to do. For me though, it still does not work when there is a tied score (mine is repeating the rank similar to the LARGE function does). See data below. Person 1 ranking is OK but Person 2 repeats rank #3. Person 3 is OK and shows that the scores don't have to be in any specific order for the formula to work. What am I doing wrong? Here is a copy/paste of the formula I am using in cell C2:
=COUNTIFS(B$2:B$31,">"&B2,$A$2:$A$31,A2)+1

Person	SCORE	RANK
Person 1	100	1
Person 1	90	2
Person 1	80	3
Person 1	70	4
Person 1	60	5
Person 1	50	6
Person 1	40	7
Person 1	30	8
Person 1	20	9
Person 1	10	10
Person 2	100	1
Person 2	90	2
Person 2	80	3
Person 2	80	3
Person 2	60	5
Person 2	50	6
Person 2	40	7
Person 2	30	8
Person 2	20	9
Person 2	10	10
Person 3	95	1
Person 3	60	7
Person 3	70	6
Person 3	10	10
Person 3	15	9
Person 3	16	8
Person 3	85	2
Person 3	84	3
Person 3	83	4
Person 3	82	5

<tbody>
</tbody><colgroup><col><col><col></colgroup>

 

[jorismoerings]

Hi,

You've applied the formula correct but your data has changed. You're trying to apply the formula which was meant for ranking alphanumeric values onto a series of numeric values. If it only was this simple....
If you want to sort the numeric values in ascending or descending order you'll need to apply some kind of RANK IF construction.
As it's Excel there are many solution for a RANK IF, this is one:

	A	B	C
1	Person	SCORE	RANK
2	Person 1	100	1
3	Person 1	90	2
4	Person 1	80	3
5	Person 1	70	4
6	Person 1	60	5
7	Person 1	50	6
8	Person 1	40	7
9	Person 1	30	8
10	Person 1	20	9
11	Person 1	10	10
12	Person 2	100	1
13	Person 2	90	2
14	Person 2	80	3
15	Person 2	80	4
16	Person 2	60	5
17	Person 2	50	6
18	Person 2	40	7
19	Person 2	30	8
20	Person 2	20	9
21	Person 2	10	10
22	Person 3	95	1
23	Person 3	60	7
24	Person 3	70	6
25	Person 3	10	10
26	Person 3	15	9
27	Person 3	16	8
28	Person 3	85	2
29	Person 3	84	3
30	Person 3	83	4
31	Person 3	82	5

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C2 =COUNTIFS($A$2:$A$31,$A2,$B$2:$B$31,">"&$B2)+COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Hope this helps

 

[birkato]

That solution works great! thanks for the help!

 

[mikerickson]

Another way to break ties is to add the row data (divided by 1000) as a tie breaker.

Unknown
	A	B	C	D
1	Score A	100		Score J
2	Score B	90		Score I
3	Score C	80		Score H
4	Score D	80		Score G
5	Score E	70		Score F
6	Score F	60		Score E
7	Score G	50		Score C
8	Score H	40		Score D
9	Score I	30		Score B
10	Score J	20		Score A
Sheet1

Cell Formulas
Range	Formula
D1	{=INDEX($A$1:$A$10, 1000*MOD(SMALL($B$1:$B$10+ROW($B$1:$B$10)/1000,ROWS($1:1)),1), 1)}
D2	{=INDEX($A$1:$A$10, 1000*MOD(SMALL($B$1:$B$10+ROW($B$1:$B$10)/1000,ROWS($1:2)),1), 1)}
D3	{=INDEX($A$1:$A$10, 1000*MOD(SMALL($B$1:$B$10+ROW($B$1:$B$10)/1000,ROWS($1:3)),1), 1)}
D4	{=INDEX($A$1:$A$10, 1000*MOD(SMALL($B$1:$B$10+ROW($B$1:$B$10)/1000,ROWS($1:4)),1), 1)}
D5	{=INDEX($A$1:$A$10, 1000*MOD(SMALL($B$1:$B$10+ROW($B$1:$B$10)/1000,ROWS($1:5)),1), 1)}
D6	{=INDEX($A$1:$A$10, 1000*MOD(SMALL($B$1:$B$10+ROW($B$1:$B$10)/1000,ROWS($1:6)),1), 1)}
D7	{=INDEX($A$1:$A$10, 1000*MOD(SMALL($B$1:$B$10+ROW($B$1:$B$10)/1000,ROWS($1:7)),1), 1)}
D8	{=INDEX($A$1:$A$10, 1000*MOD(SMALL($B$1:$B$10+ROW($B$1:$B$10)/1000,ROWS($1:8)),1), 1)}
D9	{=INDEX($A$1:$A$10, 1000*MOD(SMALL($B$1:$B$10+ROW($B$1:$B$10)/1000,ROWS($1:9)),1), 1)}
D10	{=INDEX($A$1:$A$10, 1000*MOD(SMALL($B$1:$B$10+ROW($B$1:$B$10)/1000,ROWS($1:10)),1), 1)}
Press CTRL+SHIFT+ENTER to enter array formulas.