Combine cells that fall within one range

Question

I have a set of data with numbers in Column A, then in column B and C there is data in multiple rows per number. I would like to combine each column of data such that there is only one row per number.

For example, what I have is:

Column A	Column B	Column C
1	Lorem	red
	ipsum	orange
	dolor	yellow
	sit	green
	amet	blue
2	Lorem	red
	ipsum	orange
	dolor	yellow
3	Lorem	red
	ipsum	orange
	dolor	yellow
	sit	green

And what I would like to get is:

Column A	Column B	Column C
1	Lorem ipsum dolor sit amet	red orange yellow green blue
2	Lorem ipsum dolor	red orange yellow
3	Lorem ipsum dolor sit	red orange yellow green

Unfortunately the number of rows between each number in column A varies. Is there a way I can do this with VBA?

I've used textjoin to combine cells manually, but I can't figure out how to specify the ranges, and then continue for the entire document.

Answer 1

Dynamic spill formula for Microsoft-365 user's.

=LET(i,SCAN(A2,A2:A13,LAMBDA(a,x,IF(x="",a,x))),
j,UNIQUE(i),
k,MAP(j,LAMBDA(t,TEXTJOIN(" ",TRUE,FILTER(B2:B13,i=t)))),
l,MAP(j,LAMBDA(m,TEXTJOIN(" ",TRUE,FILTER(C2:C13,i=m)))),
HSTACK(j,k,l))

Answer 2

Transform Data To Delimited

=LET(Data,A2:D21,Delimiter,", ",
    uData,TAKE(Data,,1),u,TOCOL(uData,3),urs,SEQUENCE(ROWS(uData)),
    vData,DROP(Data,,1),vcs,SEQUENCE(,COLUMNS(vData)),
    vss,FILTER(urs,(uData<>"")*urs),vrs,DROP(VSTACK(vss-1,ROWS(Data)),1)-vss+1,
    v,DROP(REDUCE("",SEQUENCE(ROWS(u)),LAMBDA(rRes,rr,LET(
        vr,INDEX(vData,SEQUENCE(INDEX(vrs,rr,1),,INDEX(vss,rr,1)),vcs),
    VSTACK(rRes,BYCOL(vr,LAMBDA(br,TEXTJOIN(Delimiter,0,br))))))),1),
Result,HSTACK(u,v),Result)

• Replace the last occurrence of Result with any of the variables to better understand how it works. You can't see the results of the lambda variables though.