Is there an easy way to sort one table to match another?

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,482
Office Version
  1. 365
Platform
  1. Windows
The columns below represent two sets of data. Columns C-E is one set and columns F-H is the other.

Is there a way that I can sort rows 5-17 in columns F-H so that the data in column F matches that in column C? That is, can I sort columns F-H so that the Teams are in the same order as they are in column C?

C/RCDEFGH
4TeamW-LBPITeamW-LBPI
5Mil50-1739.30%Mil50-1740.00%
6GSW45-2117.80%GSW45-2020.20%
7Tor48-1914.20%Tor47-1912.10%
8Bos41-267.70%Bos41-267.60%
9Den43-226.00%Den43-225.70%
10Hou41-253.80%Uta37-283.60%
11Uta37-283.70%Hou40-253.50%
12Okc40-262.30%Okc40-262.20%
13Phi42-251.80%Ind42-241.80%
14Por40-261.40%Por40-261.40%
15Ind42-251.30%Phi41-251.30%
16SA38-290.40%SA37-290.30%
17LAC38-290.20%LAC38-290.20%

<tbody>
</tbody>

Thanks
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Try selecting range C4:E17 and custom-sorting it by the Team column; then do the same with range F4:H17. Both datasets will have the teams in the same order.

Another approach: convert range C4:E17 into a table, and range F4:H17 into another table; then sort each table by their Team column.
 
Upvote 0
Try selecting range C4:E17 and custom-sorting it by the Team column; then do the same with range F4:H17. Both datasets will have the teams in the same order.

Another approach: convert range C4:E17 into a table, and range F4:H17 into another table; then sort each table by their Team column.
Excellent suggestion. That worked great. Thanks.

If I can impose on you again, I now need to sort both tables according to the BPI scores of the teams in the first table. The catch is that I will be adding a new table every day. Now I know I can select all of the columns and sort on them all on the first BPI column, but that will get difficult when I have 20-30 tables.

Is there a way to sort a named range? If so, I could name the current range (C4:H17) and sort on E. Then when I add a new table, it will get inserted between columns B & C, I can rename the range and sort again.

Or is there a better way?

I know, put it in a database. But other than that?

Here's the current set of tables:

C/R
CDEFGHIJKLMN
43/15/193/14/193/13/193/12/19
5TeamW-LBPITeamW-LBPITeamW-LBPITeamW-LBPI
6Bos42-276.10%Bos41-276.40%Bos41-276.40%Bos41-276.70%
7Den45-226.40%Den44-226.70%Den44-226.70%Den43-226.20%
8GSW46-2117.70%GSW46-2117.80%GSW45-2117.40%GSW45-2118.00%
9Hou42-263.40%Hou42-263.40%Hou42-253.80%Hou42-254.10%
10Ind44-251.40%Ind43-251.40%Ind43-251.30%Ind42-251.50%
11LAC39-300.20%LAC39-300.20%LAC39-300.20%LAC39-290.30%
12Mil51-1742.40%Mil51-1742.30%Mil51-1742.60%Mil50-1740.80%
13Okc42-272.70%Okc42-262.80%Okc41-262.70%Okc41-262.90%
14Phi43-251.80%Phi43-251.90%Phi43-251.90%Phi42-252.10%
15Por41-261.80%Por41-261.80%Por41-261.80%Por40-261.50%
16SA39-290.40%SA39-290.40%SA39-290.40%SA38-290.40%
17Tor49-2012.00%Tor48-2011.80%Tor48-2011.90%Tor48-2012.30%
18Uta39-293.70%Uta38-293.20%Uta37-292.90%Uta37-293.10%

<tbody>
</tbody>
 
Upvote 0
I found an old thread from 2011 that contains code that is supposed to sort a named range.

https://www.mrexcel.com/forum/excel-questions/579032-sorting-data-named-range.html

Here's the code:

Code:
Sub Test()
    ActiveWorkbook.Worksheets("Lists").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Lists").Sort.SortFields.Add Key:=Range("CurrencyDetails"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Lists").Sort
        .SetRange Range("CurrencyDetails")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

If I understand that thread correctly, "Lists" is the name of the worksheet and "CurrencyDetails" is the name of the named range to be sorted. But which column is it sorting on? And can I tell it to sort on a named column?
 
Last edited:
Upvote 0
... Now I know I can select all of the columns and sort on them all on the first BPI column, but that will get difficult when I have 20-30 tables...
After inserting a new dataset and re-defining your named range to include the newly added data, you can select the entire named range by pressing Ctrl+G and double-clicking its name in the "Go To" list. Then, just custom-sort the selection as needed.
 
Upvote 0
After inserting a new dataset and re-defining your named range to include the newly added data, you can select the entire named range by pressing Ctrl+G and double-clicking its name in the "Go To" list. Then, just custom-sort the selection as needed.

Yes! That works. And, if I add an empty dataset on the end where the next new one will go and define the named range to include it, then I can automatically extend the named range by merely being careful to copy the empty dataset inward -- toward the table rather than away from it.

Thank you
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top