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

[sf_adam]

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.

[#2 · Accepted solution]

@sf_adam 

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