best in series macro
 

In excel office 2003. I am entering a large number of numerical scores for
different individuals plus a few alpha digits NR, DQ etc. I want to sort the
scores in descending order left to right BUT also keep the alpha digits on
the right?
I then want to total the best 12 scores from each individual so as to
produce a positional league table from those totals. Can anyone help !!

best in series macro
 

Let's clarify - are you saying that each cell will contain BOTH numbers and
alphas?

"Derick" wrote:

In excel office 2003. I am entering a large number of numerical scores for
different individuals plus a few alpha digits NR, DQ etc. I want to sort the
scores in descending order left to right BUT also keep the alpha digits on
the right?
I then want to total the best 12 scores from each individual so as to
produce a positional league table from those totals. Can anyone help !!

best in series macro
 

No one value each cell the NR (not returned) DQ (disqualified) occupy their
own cells. When I sort the data descending the alphas move to the left which
makes selecting the best 12 scores an uneven selection
NR NR DQ 36 35 32
NR 36 35 32 33 ETC

"Duke Carey" wrote:

Let's clarify - are you saying that each cell will contain BOTH numbers and
alphas?

"Derick" wrote:

In excel office 2003. I am entering a large number of numerical scores for
different individuals plus a few alpha digits NR, DQ etc. I want to sort the
scores in descending order left to right BUT also keep the alpha digits on
the right?
I then want to total the best 12 scores from each individual so as to
produce a positional league table from those totals. Can anyone help !!

best in series macro
 

What are you doing with the 12 best scores? Summing them? Or do you need to
present each of them elsewhere?

If it's a sum, you can use this array formula - entered by pressing
Ctrl-Shift-Enter

=sum(large(scores range,row(1:12)))

if you need to extract the 12 best individual scores, you can use 12 of
these formulas (not array formulas - enter them normally)

=large(scores range,n)
where n = 1 thru 12






"Derick" wrote:

No one value each cell the NR (not returned) DQ (disqualified) occupy their
own cells. When I sort the data descending the alphas move to the left which
makes selecting the best 12 scores an uneven selection
NR NR DQ 36 35 32
NR 36 35 32 33 ETC

"Duke Carey" wrote:

Let's clarify - are you saying that each cell will contain BOTH numbers and
alphas?

"Derick" wrote:

In excel office 2003. I am entering a large number of numerical scores for
different individuals plus a few alpha digits NR, DQ etc. I want to sort the
scores in descending order left to right BUT also keep the alpha digits on
the right?
I then want to total the best 12 scores from each individual so as to
produce a positional league table from those totals. Can anyone help !!

best in series macro
 

"Duke Carey" wrote:

What are you doing with the 12 best scores? Summing them? Or do you need to
present each of them elsewhere?

If it's a sum, you can use this array formula - entered by pressing
Ctrl-Shift-Enter

=sum(large(scores range,row(1:12)))

if you need to extract the 12 best individual scores, you can use 12 of
these formulas (not array formulas - enter them normally)

=large(scores range,n)
where n = 1 thru 12






"Derick" wrote:

No one value each cell the NR (not returned) DQ (disqualified) occupy their
own cells. When I sort the data descending the alphas move to the left which
makes selecting the best 12 scores an uneven selection
NR NR DQ 36 35 32
NR 36 35 32 33 ETC

"Duke Carey" wrote:

Let's clarify - are you saying that each cell will contain BOTH numbers and
alphas?

"Derick" wrote:

In excel office 2003. I am entering a large number of numerical scores for
different individuals plus a few alpha digits NR, DQ etc. I want to sort the
scores in descending order left to right BUT also keep the alpha digits on
the right?
I then want to total the best 12 scores from each individual so as to
produce a positional league table from those totals. Can anyone help !!

best in series macro
 

Sorry I seem to have resent a previous reply.
Thanks for the help I will try the array etc and let you know. I have been
sorting each row individually then using the basic formula =sum (range 1:12)
and copying it into the 60 other 'total' cells. The sorting has been taking
forever - row by row then moving the 'alphas' back to the right. regards

"Duke Carey" wrote:

What are you doing with the 12 best scores? Summing them? Or do you need to
present each of them elsewhere?

If it's a sum, you can use this array formula - entered by pressing
Ctrl-Shift-Enter

=sum(large(scores range,row(1:12)))

if you need to extract the 12 best individual scores, you can use 12 of
these formulas (not array formulas - enter them normally)

=large(scores range,n)
where n = 1 thru 12






"Derick" wrote:

No one value each cell the NR (not returned) DQ (disqualified) occupy their
own cells. When I sort the data descending the alphas move to the left which
makes selecting the best 12 scores an uneven selection
NR NR DQ 36 35 32
NR 36 35 32 33 ETC

"Duke Carey" wrote:

Let's clarify - are you saying that each cell will contain BOTH numbers and
alphas?

"Derick" wrote:

In excel office 2003. I am entering a large number of numerical scores for
different individuals plus a few alpha digits NR, DQ etc. I want to sort the
scores in descending order left to right BUT also keep the alpha digits on
the right?
I then want to total the best 12 scores from each individual so as to
produce a positional league table from those totals. Can anyone help !!