SOLVED

# Using a value to set the ending row for a TextJoin formula

Copper Contributor

# Using a value to set the ending row for a TextJoin formula

I'd appreciate some advice on how to use the information in the 'count' or 'helper' columns (in the image below) to automatically set the end row of the textjoin formula so that I can create the information in column E automatically.

For example, how can I have Excel know that the end row for cell E9 should be 15 (aka row 9+ (count of 7 minus 1)= row 15) and that for cell E27 it should be 29 (27+(3-1)=29) Thank you.

best response confirmed by sf_adam (Copper Contributor)
Solution

# Re: Using a value to set the ending row for a TextJoin formula

In E2:

=IF(C2=C1,"",TEXTJOIN("; ",TRUE,FILTER(\$D\$2:\$D\$1000000,\$C\$2:\$C\$1000000=C2)))

or

=IF(A2="FIRST", TEXTJOIN("; ", TRUE, D2:INDEX(D3:D\$1000000, MATCH("FIRST", A3:A\$1000000, 0)-1)), "")

Fill down

1 best response

Accepted Solutions
best response confirmed by sf_adam (Copper Contributor)
Solution

# Re: Using a value to set the ending row for a TextJoin formula

In E2:

=IF(C2=C1,"",TEXTJOIN("; ",TRUE,FILTER(\$D\$2:\$D\$1000000,\$C\$2:\$C\$1000000=C2)))

or

=IF(A2="FIRST", TEXTJOIN("; ", TRUE, D2:INDEX(D3:D\$1000000, MATCH("FIRST", A3:A\$1000000, 0)-1)), "")

Fill down