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