Combine XLOOKUP and RANK to create ranked dynamic table from static alphabetical table?

[chasfh]

I have a static table of players in alphabetical order with scores and ranks. I want to create a dynamic second table that reflects the same table in ranked order of points.

My first instinct is that it would combine XLOOKUP and RANK into a single command, but that may not be the way to go, because I am coming up dry when I google around for such a command.

Here is the table in alphabetical order:

Player	Score	Rank
Barrett, S	84	11
Clark, R	86	8
Duski, M	86	8
Eikenberry, J	84	11
Helseth, T	89	3
Hopper, B	89	3
Hultquist, B	89	3
James, B	89	3
Kay, G	89	3
Michel, G	85	10
Muntz, R	94	1
Rankin, T	83	14
Rosen, R	84	11
Stephens, C	94	1
Tellman, M	82	15

The result would look something like this:

Player	Score	Rank
Muntz, R	94	1
Stephens, C	94	1
Helseth, T	89	3
Hopper, B	89	3
Hultquist, B	89	3
James, B	89	3
Kay, G	89	3
Clark, R	86	8
Duski, M	86	8
Michel, G	85	10
Barrett, S	84	11
Eikenberry, J	84	11
Rosen, R	84	11
Rankin, T	83	14
Tellman, M	82	15

The main conditions for this new table must be these:

• It has to be dynamic so that when scores and ranks change, this second table reflects the new rankings, changing the order of players listed as appropriately ranked.
• When there is a tie score between or among multiple records, all the tied players must show in the table. For example, there is a five-way tie for third on the example table—in the dynamic table result, all five players must be reflected, as opposed to the same player showing up five times.

Is this possible to do?

 

[LogChief]

Try this:

=SORT(A2:C16, 2, -1)

or this if you want ties in alphabetical order:

=SORT(A2:C16,{2,1},{-1,1})

 

[alansidman]

Power Query Solution

Book8
	A	B	C	D	E	F	G
1	Player	Score	Rank		Player	Score	Rank
2	Barrett, S	84	11		Muntz, R	94	1
3	Clark, R	86	8		Stephens, C	94	1
4	Duski, M	86	8		Helseth, T	89	3
5	Eikenberry, J	84	11		Hopper, B	89	3
6	Helseth, T	89	3		Hultquist, B	89	3
7	Hopper, B	89	3		James, B	89	3
8	Hultquist, B	89	3		Kay, G	89	3
9	James, B	89	3		Clark, R	86	8
10	Kay, G	89	3		Duski, M	86	8
11	Michel, G	85	10		Michel, G	85	10
12	Muntz, R	94	1		Barrett, S	84	11
13	Rankin, T	83	14		Eikenberry, J	84	11
14	Rosen, R	84	11		Rosen, R	84	11
15	Stephens, C	94	1		Rankin, T	83	14
16	Tellman, M	82	15		Tellman, M	82	15
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Sorted Rows" = Table.Sort(Source,{{"Score", Order.Descending}})
in
    #"Sorted Rows"

any changes to the source document will dynamically update the output when you select Data-->Refresh Data

 

[LogChief]

Or if you need to include rank in the formula:

=LET(
    a, A2:A16,
    b, B2:B16,
    c, RANK.EQ(--b, b),
    SORT(HSTACK(a, b, c), {2, 1}, {-1, 1})
)

 

[chasfh]

I appreciate your efforts but none of these are exactly what I am looking for.

First of all, what I am looking for is a dynamic second table that will automatically sort by highest to lowest point total when I update the original table. Mr. LogChief's formulas don't do that. The second formula with LET might come close but this is the result:

There are additional columns because I gave you a simplified example, but the point is, the far left column is what I wanted to sort by, and as you can see, it's out of order.

And Mr. alansidman's Power Query is too advanced for me at the moment, in that I went to this web page hoping I could easily implement it, and I can't, because the instructions are not super exact, which I owuld need, and I would have no idea how to diagnose a less than 100% perfect Power Query.

Is there anything else that could possible work here?

 

[alansidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

The Complete Guide to Installing Power Query - Excel Campus

Learn what version you need to get Power Query and instructions on how to install this free add-in from Microsoft.

www.excelcampus.com

- Follow this link for an introduction to Power Query functionality.

Power Query Overview: An Introduction to Excel's Most Powerful Data Tool - Excel Campus

Learn how this FREE Excel add-in from Microsoft called Power Query will help transform your data into a format that is ready for pivot tables, reports and more.

www.excelcampus.com

- Follow this link for a video which demonstrates how to use Power Query code provided.

Power Query - How To Paste Code (video) - Excel Solutions

Power Query is great at solving data transformation and consolidation issues. But there are still loads of Excel users who have never used Power Query yet – and sometimes when they’re given some Power Query code to answer their question, they don’t know what to do with it. Here’s a short video...

excel.solutions

 

[chasfh]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

The Complete Guide to Installing Power Query - Excel Campus

Learn what version you need to get Power Query and instructions on how to install this free add-in from Microsoft.

www.excelcampus.com

- Follow this link for an introduction to Power Query functionality.

Power Query Overview: An Introduction to Excel's Most Powerful Data Tool - Excel Campus

Learn how this FREE Excel add-in from Microsoft called Power Query will help transform your data into a format that is ready for pivot tables, reports and more.

www.excelcampus.com

- Follow this link for a video which demonstrates how to use Power Query code provided.

Power Query - How To Paste Code (video) - Excel Solutions

Power Query is great at solving data transformation and consolidation issues. But there are still loads of Excel users who have never used Power Query yet – and sometimes when they’re given some Power Query code to answer their question, they don’t know what to do with it. Here’s a short video...

excel.solutions

Thanks. I'll look into at some point. Probably not for this because I have many other things on my plate at this second.

 

[Tupacandres]

If it works for you with macro, try this way.

There may be a way to perfect the code but it is functional.

Libro1.xlsm | Powered by Box

app.box.com

 

[Tupacandres]

Si te funciona con macro prueba de esta manera.

Puede haber una manera de perfeccionar el código, pero es funcional.

https://app.box.com/s/s84egzgiisfaymcm7b1cjaxhy01zd6b2

In a module, I put the following code:

Sub SincTable()

    Dim Tbl1 As ListObject
    Dim Tbl2 As ListObject
    Dim Rw1 As ListRow
    Dim Rw2 As ListRow
    Dim i As Long, j As Long
    Dim id As Variant
    Dim ColID As Long
    Dim Found As Boolean
    Dim ColSort As Integer
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    ' Set sheets an tables
    Set Tbl1 = Sheets("Hoja1").ListObjects("Tabla1")  ' Change the sheets name and the table name
    Set Tbl2 = Sheets("Hoja1").ListObjects("Tabla2")  ' Change the sheets name and the table name
    
    ColID = 1                                       ' Change this name to index column of players name
    
    For Each Rw1 In Tbl1.ListRows
        id = Rw1.Range.Cells(1, ColID).Value
        Found = False
        For Each Rw2 In Tbl2.ListRows
            If Rw2.Range.Cells(1, ColID).Value = id Then
                Found = True
                For j = 1 To Tbl1.ListColumns.Count
                    If Rw1.Range.Cells(1, j).Value <> Rw2.Range.Cells(1, j).Value Then
                        Rw2.Range.Cells(1, j).Value = Rw1.Range.Cells(1, j).Value
                    End If
                Next j
                Exit For
            End If
        Next Rw2
        
        ' This line add in table 2 the register if not exist
        If Not Found Then
            Tbl2.ListRows.Add
            For j = 1 To Tbl1.ListColumns.Count
                Tbl2.ListRows(Tbl2.ListRows.Count).Range.Cells(1, j).Value = Rw2.Range.Cells(1, j).Value
            Next j
        End If
    Next Rw1
       
    ' Sort
    ' Name of the table header to sort by (Rank)
    ColSort = Tbl2.ListColumns("Rank").Index ' Change "Rank" if your table header is other

    ' Sort ascending
    Tbl2.Sort.SortFields.Clear
    Tbl2.Sort.SortFields.Add Key:=Tbl2.ListColumns(ColSort).Range, _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers

    Tbl2.Sort.Header = xlYes
    Tbl2.Sort.Apply
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
End Sub

And on sheet 1 to automate the process, place:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Sheets("Hoja1").ListObjects("Tabla1").Range) Is Nothing Then
        Call SincTable
    End If
End Sub

You should also update the formula in column C, the other table don't need it.
My Excel version is in spanish and maybe you get an error.