Display initial list 3 times sorted on different criteria

[3Imed]

Hi All,
I have an initial list.
I would like to be able to display this initial list 3 times sorted on different criteria each time as below:
[TABLE="width: 500"]
<tbody>[TR]
[TD]initial list[/TD]
[TD]list sorted on column C[/TD]
[TD]list sorted on column D[/TD]
[TD]list sorted on column Total[/TD]
[/TR]
[TR]
[TD][TABLE="width: 200"]
<tbody>[TR]
[TD]Rank[/TD]
[TD]Name[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]name1[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD="align: center"]30[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]name2[/TD]
[TD]15[/TD]
[TD]25[/TD]
[TD="align: center"]40[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]name3[/TD]
[TD]12[/TD]
[TD]26[/TD]
[TD="align: center"]38[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 200"]
<tbody>[TR]
[TD]Rank[/TD]
[TD]Name[/TD]
[TD="align: center"]C[/TD]
[TD]D[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]name2[/TD]
[TD]15[/TD]
[TD]25[/TD]
[TD="align: center"]40[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]name3[/TD]
[TD]12[/TD]
[TD]26[/TD]
[TD="align: center"]38[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]name1[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD="align: center"]30[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 200"]
<tbody>[TR]
[TD]Rank[/TD]
[TD]Name[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]name3[/TD]
[TD]12[/TD]
[TD]26[/TD]
[TD="align: center"]38[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]name2[/TD]
[TD]15[/TD]
[TD]25[/TD]
[TD="align: center"]40[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]name1[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD="align: center"]30[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 200"]
<tbody>[TR]
[TD]Rank[/TD]
[TD]Name[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]name2[/TD]
[TD]15[/TD]
[TD]25[/TD]
[TD="align: center"]40[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]name3[/TD]
[TD]12[/TD]
[TD]26[/TD]
[TD="align: center"]38[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]name1[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD="align: center"]30[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


What would be the best way to only update the original list and have any change reflected on the sorted display?


best
Imed

 

[mumps]

Do you want to sort the list when you make changes in columns C, D or Total? Do you want the sorted lists to appear each in a separate worksheet?

 

[3Imed]

Hi Mumps,
the changes are made only in the "initial/original list".
I would prefer to have the 3 sorted lists on the same worksheet as the "initial/original list" one.
best

 

[mumps]

Is the original list always sorted according to rank in ascending order or sorted by name to start with? Also just to clarify, do you want to sort by C when you make changes in that column, sort by D when you make a change in that column and sort by Total when you make changes in that column? Is the Total value the result of a formula?

 

[3Imed]

Changes are only done in the "original list" for which sorting/ranking is not important (changes may be due to mistakes while entering data).
Any changes done in "original list" should appear in the 3 other sorted lists according to their respective column.
Yes: [Column Total] = [Column C] + [Column D]

best

 

[mumps]

I understand that the changes are only done in the "original list". But I need to know if the name or rank will ever change and if it does, do you want the data sorted or do you want to sort only if column C or D changes?

 

[3Imed]

Thank you for your questions !
To clarify the case please consider the "original list" as a list of players evaluated on 3 criteria: Column C, Column D and their Total).

Name, C, and D are the columns where the changes may occur (in fact the rank column from the "original list" can be removed).
The need beeing to display the ranking according to column C then D and globally on the Total

 

[mumps]

Start by deleting the 'Rank' column. Copy and paste this macro into the worksheet code module. Do the following: right click the tab for "Sheet1" and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make any change and exit the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:C")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets("Sheet1").Sort.SortFields.Clear
    Sheets("Sheet1").Sort.SortFields.Add Key:=Range("B2:B" & LastRow), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With Sheets("Sheet1").Sort
        .SetRange Range("A1:D" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Sheets("Sheet1").Range("A1:D" & LastRow).Copy Sheets("Sheet1").Cells(1, 6)
    
    Sheets("Sheet1").Sort.SortFields.Clear
    Sheets("Sheet1").Sort.SortFields.Add Key:=Range("C2:C" & LastRow), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With Sheets("Sheet1").Sort
        .SetRange Range("A1:D" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Sheets("Sheet1").Range("A1:D" & LastRow).Copy Sheets("Sheet1").Cells(1, 11)
    
    Sheets("Sheet1").Sort.SortFields.Clear
    Sheets("Sheet1").Sort.SortFields.Add Key:=Range("D2:D" & LastRow), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With Sheets("Sheet1").Sort
        .SetRange Range("A1:D" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Sheets("Sheet1").Range("A1:D" & LastRow).Copy Sheets("Sheet1").Cells(1, 16)
    Application.ScreenUpdating = True
End Sub

 

[3Imed]

5/5 !!!
Thank you Mumps for your help !
really appreciated, best
Imed

 

[mumps]

My pleasure.