Merge Multiple Lines into One Cell Reference Noncontiguous Range

[Luke7731]

I have two columns, one with the case number, and the other with varying amount of rows of related case notes (so varying amount of blank rows under each case number). Hundreds of cases, thousands of rows of case notes.
I’ve attached an image of what I have vs what I need. I need the case notes to remain one line on top of the next but I need all of the case notes associated with a case number to be in a single cell next to that case number cell.
So an automated way to merge multiple individual lines of text of various size into a single cell, grouped based on the case numbers to the side.

Thanks!

 

[Peter_SSs]

I was playing with a formula:

Have you given it a test with appropriate sized data?

Hundreds of cases, thousands of rows of case notes.

 

[Georgiboy]

Have you given it a test with appropriate sized data?

I tested it wih 11000 rows of data, it works - takes around 30 seconds to calculate.

 

[Georgiboy]

The below version is about 17 seconds for 11K+ rows:

=LET(a,$A$5:$A$11194,b,$B$5:$B$11194,c,ROW(a),u,SORT(FILTER(a,LEN(a)),1,1),HSTACK(u,BYROW(u,LAMBDA(br,TEXTJOIN(CHAR(10),,FILTER(b,LOOKUP(c,IF(LEN(a),c), a)=br))))))

 

[Peter_SSs]

I tested it wih 11000 rows of data, it works - takes around 30 seconds to calculate.

Fair enough. I hadn't tested your formula, but I guessed that it would be pretty slow on data that big.