Ranking Data 1

[shytott]

Hi
I have a list of 5 players whos scores are constantly changing.
The data in col B comes from another part of the sheet, so what Im wanting to do is have the list to be re ranked (high to low) after new data is added. Presumably by running a macro? The update can be displayed anywhere, it doesn’t have to be in Cols B and C. I have tried the standard sort on Cols B and C, but Excel returns gobbledegook (Im presuming thats because all cells contain formulae?).

Many Thanks

 

[kwbMitel]

I am not an expert so take this with a grain of salt but here is what I would do.

Manual Method
Copy your fields to another area of the sheet using paste Special and only copy the values.
Then sort

Macro Method
Set macro to record the manual method


**********************************************
What's most important is that you realise ... There is no spoon.

 

[SkipVought]

Hi,

Sort the table in J:N; Left to Right, on row 6, high to low

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Of course you could restructure your table...
[pre]
Andy 0 1 1 2
Brian 2 1 3 6
Charlie 3 0 1 4
Dave 1 2 0 3
Eddy 1 1 3 5
[/pre]

Simply SORT on the last column...
[pre]
Brian 2 1 3 6
Eddy 1 1 3 5
Charlie 3 0 1 4
Dave 1 2 0 3
Andy 0 1 1 2
[/pre]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[shytott]

kwb
Tnanks, I think I know what you mean, can give that a go.


Skip
Im not used to using Tables. Would I have to define a table, and would that then allow me to sort left to right? Would this also automatically update the ranking list and therefore not need manual sorting via a macro?


Thanks both.

 

[SkipVought]

In Excel ANY table can be sorted vertically or horizontally, via the OPTIONS button in the Data > Sort & Filter > SORT Wizard.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

If your raw data is close to:
[pre]Game Player Points
Game 1 Andy 0
Game 1 Brian 2
Game 1 Charlie 3
Game 1 Dave 1
Game 1 Eddy 1
Game 2 Andy 1
Game 2 Brian 1
Game 2 Charlie 0
Game 2 Dave 2
Game 2 Eddy 1[/pre]
simple pivot table will summarize it and sort results (difference: you will get headers and reverse columns order, i.e names in first column, points in second). Raw data in table, and pivot table reference to data will automatically include new data after refreshing.


combo

 

[SkipVought]

I've expanded on combo's PivotTable suggestion with the attached .xlsm workbook.

Be sure to Enable Content (macros).

This is an example of event automation. when you make changes in the Sample sheet, in the range of the table data, which has a Named Range of Database, or below, the two PivotTables will automatically Refresh when all columns of data have been filled.

There are all kinds of ways that the logic could be fashioned. I chose to count the occurrence of data in each cell in the table and compare that count to the number of cells in the table. When those counts are equal, I refresh the PivotTables...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim pt As PivotTable
    
    If Not Intersect(Target, [database].EntireColumn) Is Nothing Then
        If Application.CountA([database]) = [database].Rows.Count * 3 Then
        
            For Each pt In Target.Parent.PivotTables
                pt.RefreshTable
            Next
            Target.Parent.Cells(1, 1).Select
        End If
    End If
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[shytott]

Hi Skip
Thanks for posting that spreadsheet. I’m not sure how it works – ie how I’d update it. Where would I enter new data? I tried changing one of the results in the first ‘area’ – ie cols A-C by putting a daft score of 20 in Andy Game 1 expecting to see Andy jump to the top of the table, but nothing happened. Is that what is supposed to happen on your sheet? I then tried updating scores in Cols H-M but Excel wouldn’t allow that. Sorry, but I’ve not experienced Pivot Tables before!

 

[SkipVought]

When you opened my workbook (notice the extension is not .xlsx but .xlsm) did you Enable Content otherwise the macros will not work?

BTW, you enter new data in the table in columns A:C Player, Game, Points.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[shytott]

Yes, I checked that macros were enabled – or is that different to ‘Content Enabled’?

If I change any of the scores in col C to say 30, should I see a trickle down effect on that score automatically appearing somewhere in the other two displays on the sheet?

Thanks

 

[SkipVought]

Yes, as long as there are no other incomplete rows of data ANYWHERE in the table.

The code is designed to refresh the PivotTables ONLY when ALL rows of data each have a value in every cell.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[shytott]

Gotcha!

Thanks Skp, thats brilliant. Much appreciated.

 

[SkipVought]

I can and will answer any questions regarding my workbook.

I would ask, however, that you post any such questions in a new thread in Forum707, where VBA/macro questions are best addressed.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]