Sorting column with blank rows (1 Viewer)

[BobNTN]

In the attached file, tab(sheet 2016) column O.

Is there a way I can rank the teams by team total and keep the overall format of the sheet ?

 

[NBVC]

In a new column at row 9 (e.g R9) enter formula:

=IF(O9="","",RANK(O9,$O$9:$O$107))

copied down.

Is that what you need?

 

[BobNTN]

Well, yes and no.
Guess I stated it wrong.
What I am looking for is how to sort the whole sheet on the totals in column O so that I have the first place team first and so on but maintaining the layout of the sheet.
I really don't think that even sounds possible.
Sorry for the confusion.

 

[NBVC]

Ok, try this.

Assuming you want to sort in Descending order from Highest score to Lowest, then:

In R8 (i.e. row between column headers and first set of scores) enter formula:

=IF(AND($C8="",$C9=""),-999, IFERROR(INDEX($O8:$O$107,MATCH(TRUE,INDEX(ISNUMBER($O8:$O$107),0),0)),-999))

copied down.

Then select from C8 to R107 and sort by Column R descending (no headers).

If you want to reverse the sort, replace the -999's in the formula with 999's.

 

[BobNTN]

That works fine but it squashes the row height on all the 'team' rows.

 

[NBVC]

hmmm... it didn't for me.

Did you start the formula in R8? Did you select from C8 down to R107 for sorting?

The formula above got some extra spaces due to forum... maybe it didn't work properly... try again with:

=IF(AND($C8="",$C9=""),999, IFERROR(INDEX($O8:$O$107,MATCH(TRUE,INDEX(ISNUMBER($O8:$O$107),0),0)),999))

 

[BobNTN]

I got it.
Now would you mind explaining that formula ? I understand the ifs, ands, isnumbers, but not how the actual calc does it.

Thank you so much.

 

[NBVC]

Explaining is always the hardest part....

The idea is to assign a value to every row, including the spaces in between teams, so that way we can sort by that row since you only have those total scores in one row for each group of 4 rows.

Ok, let's go from inside out...

This - ISNUMBER($O8:$O$107) checks if there are numbers in that range... notice I do not have a $ before the 8 in the first part of the range... so as you copy down the formula is checking from the row you are in downwards...

The INDEX() around that is used because the MATCH() formula here is an array formula, and using the INDEX() gives an array of results without having to confirm it with CTRL+SHIFT+ENTER as is usual for Array formulas.

The MATCH(TRUE,INDEX(ISNUMBER($O8:$O$107),0),0) now checks for the first time the ISNUMBER() gives a TRUE result (as we are going down the column). It returns the position from the row you are in that the match is found, then that position is the "row" value for the INDEX() function in INDEX($O8:$O$107,MATCH(TRUE,INDEX(ISNUMBER($O8:$O$107),0),0)) so that the value returned is the value that was matched.

The IFERROR() allows me to return a large number 999 if there are no more matches (which would return an error) and those would be at the bottom where you have no more scores.

I think maybe you don't even need the AND($C8="",$C9="") part at the beginning and could get away with just: =IFERROR(INDEX($O8:$O$107,MATCH(TRUE,INDEX(ISNUMBER($O8:$O$107),0),0)),999)

It was a remnant of my testing out possibilities...

 

[BobNTN]

Thanks so much again.
I'm sure you see what I am doing..
It is 2 man teams playing on a point system.
Once all 3 days are input, just thought it would be simple to let the spreadsheet list them in order of finish (high to low) rather than looking down the list and picking them out.
I'm sure there are better ways of doing all this but it's the best way I coulod figure out.
The wreal problem I had was that each player can not help or hurt his/her team more than 4 points each day over or under their quota. But on an individual basis no limit.
Then they are adjusted each day 50% up or down of what they were over / under their quota and rounding up but not down.

Oh well, it works.
Was going to do it in Access but not everyone has Access but generally have Excel.