OFFSET and CONCATENATEMULTIPLE: can they be used together?

[itr674]

Can OFFSET be added to CONCATENATEMULTIPLE so the range will change accordingly as the formula is copied down the rows?

I am using UDF CONCATENATEMULTIPLE to combine text that in a range of cells to sentences in a single cell.

Using UDF CONCATENATEMULTIPLE to get a range of cells into one single cell.
IF(B2<>1,"",CONCATENATEMULTIPLE(C$2:C$3,""))

Then using SUBSTITUTE to add a carriage return and "*" at the beginning of each sentence.
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($E2,".","."&CHAR(10)&"* ",1),
".","."&CHAR(10)&"* ",2),".","."&CHAR(10)&"* ",3),".","."&CHAR(10)&"* ",4),".","."&CHAR(10)&"* ",5),
".","."&CHAR(10)&"* ",6),".","."&CHAR(10)&"* ",7),".","."&CHAR(10)&"* ",8),".","."&CHAR(10)&"* ",9),".","."&CHAR(10)&"* ",10),
".","."&CHAR(10)&"* ",11))

 

[itr674]

Please try at E2

=IF(B2=1,SUBSTITUTE(CONCATENATEMULTIPLE(C2:INDEX(C2:C10,MATCH(2,INDEX(1/(A2:A10=A2),)))," "),".",CHAR(10)&"*"),"")

That worked also! trying to see if I can get rid of the last "*".
The rabbit ran
* Then stopped
* then ate some fruit
* But then changed the mind
* and they all lived together
*

 

[itr674]

Messy data = messy formula, try

=SUBSTITUTE(TRIM(IF(B2<>1,"",CONCATENATEMULTIPLE(C2:INDEX(C:C,AGGREGATE(14,6,ROW(A2:A20)/(A2:A20=A2),1))," "))),".",CHAR(10)&"* ")

edit:- moved position of TRIM function.

I figured it out but you were faster!
You do not know how many times a day I say "messy", or "the problem is"...
You folks on here are the only ones that understand.
THANK YOU for the help.

 

[jasonb75]

You do not know how many times a day I say "messy", or "the problem is"...

There are some things that you can not polish, you can only roll them in glitter to try and make them look better ?

 

[itr674]

disregard...

 

[itr674]

Please try at E2

=IF(B2=1,SUBSTITUTE(CONCATENATEMULTIPLE(C2:INDEX(C2:C10,MATCH(2,INDEX(1/(A2:A10=A2),)))," "),".",CHAR(10)&"*"),"")

Bo_Ry, thank you also for your help!