3

I used this site to learn how to sort a two-column list, {string, number}.

I can't find a way to combine multiple two-column lists into a single, two-column list.

For example, in Major League Baseball (MLB) there are 6 divisions. For each division, I have a column-pair that lists that division's standings. The first column lists the teams in that division, and the second column lists their total wins.

Example data:

A B C D E F G H I J K L
1 AL East Wins AL Central Wins AL West Wins NL East Wins NL Central Wins NL West Wins
2 New York Yankees 12 Cleveland Guardians 10 Texas Rangers 11 Atlanta Braves 12 Chicago Cubs 10 Arizona Diamondbacks 8
3 Boston Red Sox 11 Chicago White Sox 9 Seattle Mariners 9 Miami Marlins 9 Cincinnati Reds 9 Colorado Rockies 8
4 Baltimore Orioles 10 Detroit Tigers 9 Los Angeles Angels 8 New York Mets 8 Milwaukee Brewers 8 Los Angeles Dodgers 7
5 Tamba Bay Rays 9 Kansas City Royals 8 Oakland Athletics 8 Philadelphia Phillies 8 Pittsburgh Pirates 6 San Francisco Giants 7
6 Toronto Blue Jays 7 Minnesota Twins 7 Houston Astros 6 Washington Nationals 6 St. Louis Cardinals 4 San Diego Padres 6

The table above has a total of 12 columns.

I'd like to then add another two columns that sort the entire league by wins. Here is an example of the first 7 rows:

M N O                                                                                                                     
1 Team Wins
2 Atlanta Braves 12
3 New York Yankees 12
4 Boston Red Sox 11
5 Texas Rangers 11
6 Baltimore Orioles 10
7 Chicago Cubs 10

The issue is that it seems every sort/lookup can only see a single column. Meaning even if you can have it look at the range A:L, it can't actually see columns B, D, F, G, J, L, all at once. I tried doing a Query Array but that didn't collate everything into a single column.

How can this be done?

The janky solution is to have another column that lists every team and uses VLOOKUP to populate wins then use SORT in another column to sort that single column of all the teams. But I'd like to cut out that middle step, if possible, just for the sake of limiting clutter/simplifying the process.

0

2 Answers 2

1

If your data is in Sheet1!A1:L6 like this:

AL West Wins Al Central Wins AL East Wins NL West Wins NL Central Wins NL East Wins
Houston Astros 12 Chicago White Sox 10 New York Yankees 12 Arizona Diamondbacks 10 Chicago Cubs 12 Atlanta Braves 10
Los Angeles Angels 2 Cleveland Guardians 6 Boston Red Sox 11 Colorado Rockies 6 Cincinnati Reds 6 Miami Marlins 6
Oakland Athletics 3 Detroit Tigers 8 Baltimore Orioles 10 Los Angeles Dodgers 8 Milwaukee Brewers 8 New York Mets 8
Seattle Mariners 4 Kansas City Royals 7 Tamba Bay Rays 9 San Diego Padres 7 Pittsburgh Pirates 7 Philadelphia Phillies 7
Texas Rangers 5 Minnesota Twins 9 Toronto Blue Jays 7 San Francisco Giants 9 St. Louis Cardinals 9 Washington Nationals 9

...you can get this:

Division Team Wins
AL West Houston Astros 12
AL East New York Yankees 12
NL Central Chicago Cubs 12
AL East Boston Red Sox 11
Al Central Chicago White Sox 10
NL West Arizona Diamondbacks 10
NL East Atlanta Braves 10
AL East Baltimore Orioles 10
AL East Tamba Bay Rays 9
Al Central Minnesota Twins 9
NL West San Francisco Giants 9
NL Central St. Louis Cardinals 9
NL East Washington Nationals 9
Al Central Detroit Tigers 8
NL West Los Angeles Dodgers 8
NL Central Milwaukee Brewers 8
NL East New York Mets 8
Al Central Kansas City Royals 7
NL West San Diego Padres 7
NL Central Pittsburgh Pirates 7
NL East Philadelphia Phillies 7
AL East Toronto Blue Jays 7
Al Central Cleveland Guardians 6
NL West Colorado Rockies 6
NL Central Cincinnati Reds 6
NL East Miami Marlins 6
AL West Texas Rangers 5
AL West Seattle Mariners 4
AL West Oakland Athletics 3
AL West Los Angeles Angels 2

...like this:

=let( 
  unpivot_, lambda(d,f,g,let(r_,lambda(t,o,h,chooserows(t,sequence(h,1,o))),c_,lambda(t,o,w,choosecols(t,sequence(1,w,o))),s_,lambda(r,c,h,w,r_(c_(d,c,w),r,h)),l_,lambda(c,s_(1,c,1,1)),hr,hstack(s_(1,1,1,f), "Division", "Team", "Wins" ),i,sequence(1,(columns(d)-f)/g,f+1,g),n,tocol(iferror(ø),true),reduce(hr,sequence(rows(d)-1,1,2),lambda(z,r,let(e,s_(r,1,1,f),s,reduce(n,i,lambda(t,c,let(x,s_(r,c,1,g),if(single(x)="",t,vstack(t,hstack(e,l_(c),x)))))),vstack(z,s)))))), 

  unpivoted, choosecols(unpivot_(hstack(arrayformula(row(Sheet1!A1:L6)), Sheet1!A1:L6), 1, 2), 2, 3, 4), 
  
  query(unpivoted, "order by Col3 desc", 1) 
)
0
0

You can get the full range as two columns with WRAPROWS, you can use also QUERY to delete empty rows:

=SORT(QUERY(WRAPROWS(A:L,2),"WHERE Col1 is not null"),2,0)
2
  • If the source data has a header row, you might want to leave out the sort() and use just =query(wraprows(A1:L, 2), "where Col1 is not null order by Col2 desc", 1). Commented Jul 7, 2023 at 7:50
  • Yeah, thought it after leaving the computer! Thanks for pointing it out!
    – Martín
    Commented Jul 7, 2023 at 7:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.