CODEWITHSUNDEEP
excel-formula
Pedro
Pedro

Reputation: 15

TEXTJOIN per table line based on heading

I need to create a string per row line that includes all elements that are present in that row but displaying the corresponding heading column text, while not representing (in the string) the element that is the label of the row. Explaining...

Consider the following table:

AAA BBB CCC DDD
AAA 1 1 1
BBB 1 1 1
BBB 1
CCC 1 1

I expect the following result (for each table row):

Column A
BBB, DDD
AAA, CCC
DDD

Take notice that in the first row (which stand for element "AAA") that element does not appear in the string - same for "BBB" in the second line and "CCC" in the last line. For that same reason, the third line has an empty string as result.

In order to solve this, I'm creating a new table with each of the table heading element (that's not the row element) and then do a TEXTJOIN with ", " as separator. As follows:

AAA BBB CCC DDD
AAA BBB DDD =TEXTJOIN(", ";TRUE;B2:B5)
BBB AAA CCC =TEXTJOIN(", ";TRUE;C2:C5)
BBB =TEXTJOIN(", ";TRUE;D2:D5)
CCC DDD =TEXTJOIN(", ";TRUE;E2:E5)

Now, for sure it may be possible to do this without the need to create this extra table - or maybe to create such a table inline in the formula ?

Upvotes: 0

Views: 97

Answers (1)

Ike
Ike

Reputation: 13024

You can use this formula:

=LET(
rH,B1:E1,
cH,A2:A5,
d,B2:E5,
BYROW(cH,LAMBDA(r,
              TEXTJOIN(",",TRUE,
                       FILTER(rH,(rH<>r)*(CHOOSEROWS(d,ROW(r)-1)=1),"")
))))

It filters - per data-row - the header-row and returns only those values from the header row that don't match the rows first cell and have a 1 in the according column of the data row.

EDIT: As per your comment - a one-liner that you have to drag down:

=LET(rH,$B$1:$E$1,cH,A2,d,B2:E2,
         TEXTJOIN(",",TRUE,
            FILTER(rH,(rH<>cH)*(d=1),"")
         )
      )

In this case you don't need the BYROW- part

Upvotes: 0

Related Questions