CODEWITHSUNDEEP
sortinggoogle-sheetsgoogle-sheets-formula
gboffi
gboffi

Reputation: 25023

sort a range consisting of a single row

                     A              B             C
1                 zoo            aardvark      gorilla
2 =sort(A1:B1) ←  aardvark       gorilla       zoo

What I need to do is sorting the first row into the second one, ideally placing a formula involving sort in the A2 cell.

Is there some (relatively) simple workaround that I can use to get what I want?

Upvotes: 0

Views: 50

Answers (1)

Aresvik
Aresvik

Reputation: 4620

To process a single row, try this in cell A2:

=split(textjoin("|",1,sort(flatten(A1:C1),1,1)),"|")

or

=transpose(sort(transpose(A1:C1),1,1))

For a more complex arrayformula, use:

=arrayformula(
{
indirect("A1:"&substitute(address(1,columns(A:C),4),"1",)&"1");
substitute(
vlookup(
sequence(max(if(A:C<>"",row(A:A)))-1,columns(A:C)),
{sequence(columns(A:C)*(max(if(A:C<>"",row(A:A)))-1)),query({array_constrain(int((row(A2:A)-2)/columns(A:C))+1,columns(A:C)*(max(if(A:C<>"",row(A:A)))-1),1),query({flatten(indirect("A2:"&substitute(address(1,columns(A:C),4),"1",)&(max(if(A:C<>"",row(A:A)))-1)+1)&char(9999))},"where Col1 is not null",0)},"order by Col1,Col2",0)}
,3,0)
,char(9999),)})

enter image description here

An adjust your range in place of A:C.

Upvotes: 2

Related Questions