Excel TEXTJOIN Range, Order is by numbering

Question

I have a range that I want to use TEXTJOIN on with delimiter CHAR(10) (linebreak) The values are the header names and the order is by the numbering that is on the row. If values are empty then ignore. see image below.

any help will be much appreciated.

Try nesting SORTBY() inside TEXTJOIN(). https://support.microsoft.com/en-au/office/sortby-function-cd2d7a62-1b93-435c-b561-d6a35134f28f — bugdrown, Jun 18 '23 at 12:16

score 4 · Accepted Answer · answered Jun 18 '23 at 12:28

4

=TEXTJOIN(CHAR(10),TRUE,SORTBY(FILTER($A$1:$D$1,A2:D2),FILTER(A2:D2,A2:D2),1))

This would go in E2 and then you can drag the formula down. It filters the fruit names according to if there are rank numbers for them in that row, and removes any that don't (e.g. Orange and Peach on row 4). It then sorts by ascending number and joins the results with a line break.

answered Jun 18 '23 at 12:28

lisboakotor

482
2
9

that worked. I changed it to table references: =TEXTJOIN(CHAR(10),TRUE,SORTBY(FILTER(Table[[#Headers],[Apple]:[Peach]],Table[@[Apple]:[Peach]]),FILTER(Table[@[Apple]:[Peach]],Table[@[Apple]:[Peach]]),1)) – Kobe2424 Jun 18 '23 at 12:51

ΑΓΡΙΑ ΠΕΣΤΡΟΦΑ · Answer 2 · 2023-06-25T14:35:54.540

Public Function makeList(items As Range, idxs As Range) As String
       Dim c As Long, ccnt As Long, i As Variant
       ccnt = items.Columns.CountLarge
       For c = 1 To ccnt
          i = idxs(1, c)
          If i <> vbNullString And IsNumeric(i) Then
             If i >= 1 And i <= ccnt Then
                makeList = makeList & IIf(makeList = vbNullString, vbNullString, vbLf) & items(1, i)
             End If
          End If
       Next
    End Function

Excel TEXTJOIN Range, Order is by numbering

2 Answers2