Auto Sorting: Lost in the Dark on this one

[KC2MF]

I've been recently trying to construct a standings chart with some success as to its presentation and form. My problem is how to auto sort it when inputting new data into the fields of the respective teams within groups.

Is this where I need a list, code? Here is the format I've made my standings worksheet . . .

Team City, Wins, Losses, Ties, Win %, Unis behind the leader

For instance . . .
Team Name, Wins, Losses, Win %
1. Team A: 95W, 67L, .586
2. Team B: 96W, 66L, .593

would be sorted so that team A would be in second place and team B would be in first. What's the best procedure to go with here? Anyone have any idea what to do? I've been stumped on this problem for weeks now and have no idea where to start. I have many more questions but I'll save them until I get a response if you have any questions.

 

[energman58]

You need to separate the display of the team standings from the data

If you do that you can use a formula that looks something like this (modified) to return the team in each rank:

=INDEX(list of teams,MATCH(LARGE(list of scores,rank number from one to however many you need),list of scores,0))

you could determine the rank number based on the row number (using ROW) so you just have one formula to copy down

The only problem with this is that if you have two teams with exactly the same score it will find the first one in the original list of data twice and never find teh second one - is this likely to be a problem (as this will require a bit more thought if it is)?

 

[KC2MF]

Thanks for responding to my post! I'm not tallying scores as I am tallying the record of the team (wins, losses ties), and the standings would be sorted by winning percentage (wins + (ties/2))/(wins+losses+ties) Since they could be tied in the standings it would only be a problem once the end of the season arrived and there would be no more games to play.

My issue would be to actively have the team drop and rise depending if their winning percentage is higher or lower in terms of the teams division and/or league. I really can't afford to use Vlookup since it will not only break the back of my worksheet space but it can't sort without secondary placement which will make it bulky as well as redundant. I'm hoping it can be done with a simple application of the new numbers rather then a brand new sheet to place the data in.

If you need any more info don't hesitate to ask.

 

[energman58]

Solved it for duplicates!

If column d has the teams in it you put the following formula in there starting in D2:

=INDEX(Team Names Data Range,MATCH(LARGE(Team Scores Data Range,ROW()-1),Team Score Data Range,0)+COUNTIF($E$2:E2,E2)-1)

And copy down

column e has the corresponding scores in it - you put the following formula in there in E2

=INDEX(Team Scores Data Range,MATCH(LARGE(Team Scores Data Range,ROW()-1),Team Scores Data Range,0))

And copy down

This places the teams in a tie situation in the order that they appear in your original data

In my version I also added a column for position in the league using RANK based on the score:

=RANK(E2,Team Score Data Range)

In C2 and copied down

This shows the tied scores as having the same rank so it returns say 1,2,3,3,5 etc.

 

[energman58]

For Team Scores Range substitute your winning percentage range (obvioulsy)

 

[KC2MF]

So I got the equation and I understand where everything goes but I still don't see how the standings will actively update themselves. Here is the way I've structured my standings on the sheet by column with the teams located on the rows. If you could tell me where your equation goes it would be greatly appreciated . . .
A Team, B Wins, C Losses, D Ties, E Games Played, F Win %, G Games Behind, H Magic/"Tragic" Number
1. Philadelphia 97, 65, 0, 162, .5988, Leader, Clinched!
2. Atlanta 91, 71, 0, 162, .5617, 6, Eliminated
3. Florida 80, 82, 0, 162, .4938, 17, Eliminated
4. New York 79, 83, 0, 162, .4877, 18, Eliminated
5. Washington 69, 93, 0, 162, .4259, 28, Eliminated

I should also mention that this is only one of multiple groups similar to this, and that some of these groups are located within a larger group or league. The real goal for me is to have the record on this small group reflect itself here and in the larger league group as well. So it's multifaceted on several worksheets.

 

[mikerickson]

You could put
In E2, put =B2+C2
In F2, put =B2/E2
In G2, put =TEXT(E2*(MAX(F:F)-F2),"[=0]""Leader"";0")

and drag down. Then code like this, in the ThisWorkbook module, will sort on entry.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim aRange As Variant, findThis As Variant

    For Each aRange In Array(Sheet1.Range("A1:H6"), Sheet2.Range("B2:I7")): Rem adjust
    
        With aRange
            If Sh.Name = .Parent.Name Then
                If Not Application.Intersect(.Cells, Target) Is Nothing Then
                    findThis = Application.Intersect(.Columns(1), Target.Cells(1, 1).EntireRow)
                    
                    .Sort Key1:=.Columns(6), Order1:=xlDescending, Key2:=.Columns(2) _
                        , Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
                        
                    Application.Intersect(.Columns(1).Find(what:=findThis).EntireRow, Target.Cells(1, 1).EntireColumn).Offset(1, 0).Select
                End If
            End If
        
        End With
    Next aRange
End Sub

 

[energman58]

Oh!

I found a mistake in the ranking thing and I used a User Derived Function which is a lot easier

I stole this from here: http://www.ozgrid.com/Excel/find-nth.htm

The UDF is

Function Nth_Occurrence(range_look As Range, find_it As String, occurrence As Long, offset_row As Long, offset_col As Long)
Dim lCount As Long
Dim rFound As Range
 
    Set rFound = range_look.Cells(1, 1)
        For lCount = 1 To occurrence
            Set rFound = range_look.Find(find_it, rFound, xlValues, xlWhole)
        Next lCount
    Nth_Occurrence = rFound.Offset(offset_row, offset_col)
End Function

Copy my code above

In your spreadsheet

Activate VBA Editor (ALT F11)

You shoudl see the name of your worksheet in the panel on the left of the screen - right click on it and click insert then module

Then paste the code from above on the sheet that opens here and close the editor

This will add a new user defined function called Nth_occurrence which returns the nth time a value appears in an array

Then in the sheet with your data in highlight the range with the win percentage in and click on the name manager (under Formulas in 2007) and call it TeamScores

Then on a new sheet insert the following

In E1 Rank
In F1 Team Name
In G1 Winning %

Then insert the formulas below:

In E2

=RANK(G2,TeamScores)

In F2

=Nth_Occurrence(TeamScores,$G2,COUNTIF($G$2:$G2,$G2),0,-5)

But here you need to make sure -5 is however many columns to the left teh team name is from the WInning % in your data - from your example I think I got it right but check

In G2

=INDEX(TeamScores,MATCH(LARGE(TeamScores,ROW()-1),TeamScores,0))

Then you copy the formulas down as far as you need and it should all work

If you want to pull in the other stats for your teams you can use lookups based on the name.

If you want to do subsets of teams then I suggest you pull the data for each group out from the master list with formula references to separate ranges which you give different names to and adapt the above formulas as needed



BTW I think you need to save it as a macro-enabled workbook to use the UDF!