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

JenniferMurphy

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,691
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?

[TABLE="class: grid, width: 326"]
<tbody>[TR]
[TD="align: center"]C/R[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Team[/TD]
[TD="align: center"]W-L[/TD]
[TD="align: center"]BPI[/TD]
[TD="align: center"]Team[/TD]
[TD="align: center"]W-L[/TD]
[TD="align: center"]BPI[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Mil[/TD]
[TD="align: center"]50-17[/TD]
[TD="align: center"]39.30%[/TD]
[TD="align: center"]Mil[/TD]
[TD="align: center"]50-17[/TD]
[TD="align: center"]40.00%[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]GSW[/TD]
[TD="align: center"]45-21[/TD]
[TD="align: center"]17.80%[/TD]
[TD="align: center"]GSW[/TD]
[TD="align: center"]45-20[/TD]
[TD="align: center"]20.20%[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]Tor[/TD]
[TD="align: center"]48-19[/TD]
[TD="align: center"]14.20%[/TD]
[TD="align: center"]Tor[/TD]
[TD="align: center"]47-19[/TD]
[TD="align: center"]12.10%[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]Bos[/TD]
[TD="align: center"]41-26[/TD]
[TD="align: center"]7.70%[/TD]
[TD="align: center"]Bos[/TD]
[TD="align: center"]41-26[/TD]
[TD="align: center"]7.60%[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]Den[/TD]
[TD="align: center"]43-22[/TD]
[TD="align: center"]6.00%[/TD]
[TD="align: center"]Den[/TD]
[TD="align: center"]43-22[/TD]
[TD="align: center"]5.70%[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]Hou[/TD]
[TD="align: center"]41-25[/TD]
[TD="align: center"]3.80%[/TD]
[TD="align: center"]Uta[/TD]
[TD="align: center"]37-28[/TD]
[TD="align: center"]3.60%[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]Uta[/TD]
[TD="align: center"]37-28[/TD]
[TD="align: center"]3.70%[/TD]
[TD="align: center"]Hou[/TD]
[TD="align: center"]40-25[/TD]
[TD="align: center"]3.50%[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]Okc[/TD]
[TD="align: center"]40-26[/TD]
[TD="align: center"]2.30%[/TD]
[TD="align: center"]Okc[/TD]
[TD="align: center"]40-26[/TD]
[TD="align: center"]2.20%[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]Phi[/TD]
[TD="align: center"]42-25[/TD]
[TD="align: center"]1.80%[/TD]
[TD="align: center"]Ind[/TD]
[TD="align: center"]42-24[/TD]
[TD="align: center"]1.80%[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]Por[/TD]
[TD="align: center"]40-26[/TD]
[TD="align: center"]1.40%[/TD]
[TD="align: center"]Por[/TD]
[TD="align: center"]40-26[/TD]
[TD="align: center"]1.40%[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]Ind[/TD]
[TD="align: center"]42-25[/TD]
[TD="align: center"]1.30%[/TD]
[TD="align: center"]Phi[/TD]
[TD="align: center"]41-25[/TD]
[TD="align: center"]1.30%[/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: center"]SA[/TD]
[TD="align: center"]38-29[/TD]
[TD="align: center"]0.40%[/TD]
[TD="align: center"]SA[/TD]
[TD="align: center"]37-29[/TD]
[TD="align: center"]0.30%[/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: center"]LAC[/TD]
[TD="align: center"]38-29[/TD]
[TD="align: center"]0.20%[/TD]
[TD="align: center"]LAC[/TD]
[TD="align: center"]38-29[/TD]
[TD="align: center"]0.20%[/TD]
[/TR]
</tbody>[/TABLE]

Thanks
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
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
Tetra201 said:
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.
Click to expand...
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:

[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD="align: center"]C/R
[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="colspan: 3, align: center"]3/15/19[/TD]
[TD="colspan: 3, align: center"]3/14/19[/TD]
[TD="colspan: 3, align: center"]3/13/19[/TD]
[TD="colspan: 3, align: center"]3/12/19[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Team[/TD]
[TD="align: center"]W-L[/TD]
[TD="align: center"]BPI[/TD]
[TD="align: center"]Team[/TD]
[TD="align: center"]W-L[/TD]
[TD="align: center"]BPI[/TD]
[TD="align: center"]Team[/TD]
[TD="align: center"]W-L[/TD]
[TD="align: center"]BPI[/TD]
[TD="align: center"]Team[/TD]
[TD="align: center"]W-L[/TD]
[TD="align: center"]BPI[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Bos[/TD]
[TD="align: center"]42-27[/TD]
[TD="align: center"]6.10%[/TD]
[TD="align: center"]Bos[/TD]
[TD="align: center"]41-27[/TD]
[TD="align: center"]6.40%[/TD]
[TD="align: center"]Bos[/TD]
[TD="align: center"]41-27[/TD]
[TD="align: center"]6.40%[/TD]
[TD="align: center"]Bos[/TD]
[TD="align: center"]41-27[/TD]
[TD="align: center"]6.70%[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]Den[/TD]
[TD="align: center"]45-22[/TD]
[TD="align: center"]6.40%[/TD]
[TD="align: center"]Den[/TD]
[TD="align: center"]44-22[/TD]
[TD="align: center"]6.70%[/TD]
[TD="align: center"]Den[/TD]
[TD="align: center"]44-22[/TD]
[TD="align: center"]6.70%[/TD]
[TD="align: center"]Den[/TD]
[TD="align: center"]43-22[/TD]
[TD="align: center"]6.20%[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]GSW[/TD]
[TD="align: center"]46-21[/TD]
[TD="align: center"]17.70%[/TD]
[TD="align: center"]GSW[/TD]
[TD="align: center"]46-21[/TD]
[TD="align: center"]17.80%[/TD]
[TD="align: center"]GSW[/TD]
[TD="align: center"]45-21[/TD]
[TD="align: center"]17.40%[/TD]
[TD="align: center"]GSW[/TD]
[TD="align: center"]45-21[/TD]
[TD="align: center"]18.00%[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]Hou[/TD]
[TD="align: center"]42-26[/TD]
[TD="align: center"]3.40%[/TD]
[TD="align: center"]Hou[/TD]
[TD="align: center"]42-26[/TD]
[TD="align: center"]3.40%[/TD]
[TD="align: center"]Hou[/TD]
[TD="align: center"]42-25[/TD]
[TD="align: center"]3.80%[/TD]
[TD="align: center"]Hou[/TD]
[TD="align: center"]42-25[/TD]
[TD="align: center"]4.10%[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]Ind[/TD]
[TD="align: center"]44-25[/TD]
[TD="align: center"]1.40%[/TD]
[TD="align: center"]Ind[/TD]
[TD="align: center"]43-25[/TD]
[TD="align: center"]1.40%[/TD]
[TD="align: center"]Ind[/TD]
[TD="align: center"]43-25[/TD]
[TD="align: center"]1.30%[/TD]
[TD="align: center"]Ind[/TD]
[TD="align: center"]42-25[/TD]
[TD="align: center"]1.50%
[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]LAC[/TD]
[TD="align: center"]39-30[/TD]
[TD="align: center"]0.20%[/TD]
[TD="align: center"]LAC[/TD]
[TD="align: center"]39-30[/TD]
[TD="align: center"]0.20%[/TD]
[TD="align: center"]LAC[/TD]
[TD="align: center"]39-30[/TD]
[TD="align: center"]0.20%[/TD]
[TD="align: center"]LAC[/TD]
[TD="align: center"]39-29[/TD]
[TD="align: center"]0.30%[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]Mil[/TD]
[TD="align: center"]51-17[/TD]
[TD="align: center"]42.40%[/TD]
[TD="align: center"]Mil[/TD]
[TD="align: center"]51-17[/TD]
[TD="align: center"]42.30%[/TD]
[TD="align: center"]Mil[/TD]
[TD="align: center"]51-17[/TD]
[TD="align: center"]42.60%[/TD]
[TD="align: center"]Mil[/TD]
[TD="align: center"]50-17[/TD]
[TD="align: center"]40.80%[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]Okc[/TD]
[TD="align: center"]42-27[/TD]
[TD="align: center"]2.70%[/TD]
[TD="align: center"]Okc[/TD]
[TD="align: center"]42-26[/TD]
[TD="align: center"]2.80%[/TD]
[TD="align: center"]Okc[/TD]
[TD="align: center"]41-26[/TD]
[TD="align: center"]2.70%[/TD]
[TD="align: center"]Okc[/TD]
[TD="align: center"]41-26[/TD]
[TD="align: center"]2.90%[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]Phi[/TD]
[TD="align: center"]43-25[/TD]
[TD="align: center"]1.80%[/TD]
[TD="align: center"]Phi[/TD]
[TD="align: center"]43-25[/TD]
[TD="align: center"]1.90%[/TD]
[TD="align: center"]Phi[/TD]
[TD="align: center"]43-25[/TD]
[TD="align: center"]1.90%[/TD]
[TD="align: center"]Phi[/TD]
[TD="align: center"]42-25[/TD]
[TD="align: center"]2.10%[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]Por[/TD]
[TD="align: center"]41-26[/TD]
[TD="align: center"]1.80%[/TD]
[TD="align: center"]Por[/TD]
[TD="align: center"]41-26[/TD]
[TD="align: center"]1.80%[/TD]
[TD="align: center"]Por[/TD]
[TD="align: center"]41-26[/TD]
[TD="align: center"]1.80%[/TD]
[TD="align: center"]Por[/TD]
[TD="align: center"]40-26[/TD]
[TD="align: center"]1.50%[/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: center"]SA[/TD]
[TD="align: center"]39-29[/TD]
[TD="align: center"]0.40%[/TD]
[TD="align: center"]SA[/TD]
[TD="align: center"]39-29[/TD]
[TD="align: center"]0.40%[/TD]
[TD="align: center"]SA[/TD]
[TD="align: center"]39-29[/TD]
[TD="align: center"]0.40%[/TD]
[TD="align: center"]SA[/TD]
[TD="align: center"]38-29[/TD]
[TD="align: center"]0.40%[/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: center"]Tor[/TD]
[TD="align: center"]49-20[/TD]
[TD="align: center"]12.00%[/TD]
[TD="align: center"]Tor[/TD]
[TD="align: center"]48-20[/TD]
[TD="align: center"]11.80%[/TD]
[TD="align: center"]Tor[/TD]
[TD="align: center"]48-20[/TD]
[TD="align: center"]11.90%[/TD]
[TD="align: center"]Tor[/TD]
[TD="align: center"]48-20[/TD]
[TD="align: center"]12.30%[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"]Uta[/TD]
[TD="align: center"]39-29[/TD]
[TD="align: center"]3.70%[/TD]
[TD="align: center"]Uta[/TD]
[TD="align: center"]38-29[/TD]
[TD="align: center"]3.20%[/TD]
[TD="align: center"]Uta[/TD]
[TD="align: center"]37-29[/TD]
[TD="align: center"]2.90%[/TD]
[TD="align: center"]Uta[/TD]
[TD="align: center"]37-29[/TD]
[TD="align: center"]3.10%[/TD]
[/TR]
</tbody>[/TABLE]
 
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
JenniferMurphy said:
... 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...
Click to expand...
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
Tetra201 said:
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.
Click to expand...

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
You must log in or register to reply here.

Similar threads

gtd526
  • Question Question
Conditional Formatting for Matching Odd Rows only
Replies
4
Views
262
gtd526
gtd526
gtd526
  • Solved
vba add column before each date listed in row (nba schedule)
Replies
2
Views
375
gtd526
gtd526
M
  • Solved
Copy selected column in range to another sheet
2
Replies
15
Views
267
motilulla
M
D
  • Solved
finding changes and looking back
2
Replies
10
Views
418
felixstraube
F
U
  • Solved
Index formula with matching - Can it look at rows and columns?
Replies
2
Views
411
usnapoleon
U

Forum statistics

Threads
1,224,618
Messages
6,179,917
Members
452,949
Latest member
beartooth91

Share this page

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