Formula/Macro help needed please

[johnboy45]

I have a spreadsheet that will contain around 50 rows and 25 columns and a sample is attached.
I already have top/bottom rules set by conditional formatting. This colour fills the top 12 value cells of each column and gives a total value of those cells.
What I also would like to do is to be able to sort each column in numerical order but can only do this by sort each column individually which is slow.
Any suggestions how I may achieve this in an easier way.

The end result should give me a total for each of the 12 highest values in each column and also indicate which is the lowest counting value in each column. (See Colums B-G)

Thank you for reading this

[hrlngrv]

Do you mean you want 'Umbrella scores'!B1:P33 sorted in place? If so, you'd need to use VBA event handlers, specifically Worksheet_Change for that worksheet to check if a cell in B1:P33 changed, and if so, for each column in B1:P33 intersecting the changed cells, sort that column in place. Can you use VBA?

Do you want to ignore columns C and D which have fewer than 12 numbers?

Otherwise, if it'd be OK to use another range for the sorted results (presumably in descending order), then let that sorted range start in R1 and span R1:AF33.

Please Login or Register  to view this content.

Fill R2 down into R3:R33. Select R1:R33 and fill right into S1:AF33. You could then just sum R1:R12, etc. Also, the values in R12:AF12 would be the 12th largest values, so the thresholds.
Note: cells not containing numbers in B1:P33 would need to be BLANK, as in, ISBLANK(cell) = TRUE or COUNTA(cell) = 0. Some of the cells in B1:P33 contain, e.g., I30, contain a single space. Such text fubars the R2 formula above. I could modify the formula to ignore text, but IMO it'd be better for the range never to contain anything other than numbers and actually blank cells.

[johnboy45]

Thank you so much for you response. I think I am out of my depth here as I do not know how to use VBA.
I should perhaps explain a bit more what I am trying to achieve.
Firstly this is a sports results table and Columns B - G show how I want to end up. Columns H - P are unaltered.
Each column label represents a team.
Each team will play many games over a season and all their scores are recorded each time they play.
Each team's own Top 12 scores give a total point count.
In order to improve their point count the team would have to score higher their previous lowest counting score.
As in the example now attached Team B would have to score 11 points or higher in order to remove their previous lowest counting score of 10. Team E, F G are similar.
Teams C & D have not yet played the minimum 12 games.

Hope this is a bit clearer.

Any solution you can offer will be very well received.

I can always fall back on the old fashioned method of pencil and paper

[JeteMc]

Hello johnboy45 and Welcome to Excel Forum.
Perhaps this will help.
1. The formula that puts the values in least to greatest order is: =IFERROR(AGGREGATE(15,6,B$1:B$33,ROWS(R$1:R1)),"")
2. The conditional formatting rule that fills cells containing top 12 values per column is: =AND(R1<>"",R1>=R$37)
3. The conditional formatting rule for the red font is: =R1=R$37
Note that both rules apply to the entire range (=$R$1:$AF$33)
Note that both rules reference cells in row 37 which is populated using: =LARGE(R1:R33,12)
Let us know if you have any questions.