In Google Sheets, is it possible to collate + auto sort multiple columns of data into a single column?

Question

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.

Answer 1

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)

Answer 2

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) 
)