CODEWITHSUNDEEP
excelvbaexcel-formula
Gary's Student
Gary's Student

Reputation: 96771

Reverse order of TEXTJOIN Output

I have been asked to remove an Excel VBA UDF from an Excel workbook. This is to allow the workbook to function in a macro-free environment.

The code reverses the order of items in a comma separated string. The code is extremely simple:

Public Function FlipIt(Sin As String) As String
    Dim a, arr

    FlipIt = ""
    arr = Split(Sin, ",")
    For Each a In arr
        FlipIt = a & "," & FlipIt
    Next a
    FlipIt = Left(FlipIt, Len(FlipIt) - 1)
End Function

and it is used in the worksheet like:

=flipit(TEXTJOIN(",",TRUE,A1:A6))

enter image description here

I wrote the code because I could not figure our how to make TEXTJOIN() output in reverse order.

The best I could come up with was to use a helper column. So in D1 I enter:

=IF(ROWS($1:1)>COUNTA(A:A),"",INDEX(A:A,COUNTA(A:A)-ROWS($1:1)+1))

and copy downwards. The use:

=TEXTJOIN(",",TRUE,D1:D6)

I can't believe that a "helper" column is required for something this simple.

Any suggestions??

Upvotes: 2

Views: 2503

Answers (2)

chris neilsen
chris neilsen

Reputation: 53136

Using the new Dynamic Array functions, this can be done as

=TEXTJOIN(",",TRUE,INDEX(A1:A6,SORT(ROW(A1:A6),1,-1)*1,1))

Or beter yet, if the source is a Spill range

=TEXTJOIN(",",TRUE,INDEX(A1#,SORT(ROW(A1#),1,-1)*1,1))

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152605

Use this array formula that creates an array of the names backwards:

=TEXTJOIN(",",TRUE,INDEX(A1:A7,N(IF({1},ROWS(A1:A7)-ROW(A1:A7)+MIN(ROW(A1:A7))))))

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

enter image description here

Once Microsoft releases SEQUENCE to Office 365 it can be shortened to:

=TEXTJOIN(",",TRUE,INDEX(A:A,SEQUENCE(ROWS(A1:A7),,ROWS(A1:A7),-1)))

Which can be entered normally with Enter.

Upvotes: 8

Related Questions