Reverse order of TEXTJOIN range of cells in a row

Question

Formula I am using in Google Sheets:

=TEXTJOIN(CHAR(10)&CHAR(10),true,C2:2)

I want to reverse the order those cells are joined so that it is always the furthest right cell first.

Please see image below for example of what I am trying to achieve.

Example

I attempted the advice here Reverse order of TEXTJOIN Output but it did not seem to translate for my needs.

Answer 1

Answer

Harun's method is correct, but I hope to provide a simpler and more efficient solution.

The following formula should produce the result you desire:

=TEXTJOIN(CHAR(10)&CHAR(10),true,SORT(TRANSPOSE(C2:2),TRANSPOSE(COLUMN(C2:2)),false))

Explanation

First, the =COLUMN formula is used to obtain column numbers for each column that we want to use =TEXTJOIN on. In this case, that is the range C2:C.

Next, the =TRANSPOSE formula works on both C2:C and =COLUMN(C2:C) to change both from being horizontal arrays to being vertical arrays. This is needed for =SORT to function correctly.

The =SORT function is then used to order the transposed results based on the transposed row numbers. The third argument of =SORT is false because we wish to sort in descending order, from highest column number to lowest.

Finally, everything is combined in the =TEXTJOIN function. Each entry returned by =SORT is joined together with two line breaks using the =CHAR function. The second argument of =TEXTJOIN is true because we want to ignore blank entries.

Functions used:

• =TEXTJOIN
• =CHAR
• =SORT
• =TRANSPOSE
• =COLUMN

Answer 2

In google-sheet try-

=TEXTJOIN(CHAR(10)&CHAR(10),1,BYROW(SEQUENCE(COUNTA(C2:2),1,COUNTA(C2:2),-1),LAMBDA(x,INDEX(C2:2,1,x))))