Consolidating substring values into a cell in Google Sheets

Question

I have a sheet where I am tracking list of books as below.

Book name
Blue Lock, Vol. 1
Naruto, Vol. 9
Harry Potter (novel), Vol. 2
Naruto, Vol 10
Girl, unframed

I have a query where I am able to consolidate all duplicates and remove suffixes.

=ARRAYFORMULA(((QUERY({QUERY(Sheet1!A:M, "select A, C, J where A is not null"), REGEXEXTRACT(QUERY(Sheet1!A:M, "select A where A is not null"), "(.*),")}, "select Col4, Count(Col4), Col2 where Col4 is not null and Col3 = 'Y' group by Col4, Col2 label Col4 'Book Name', Count(Col4) 'Volumes'"))))

I am only able to get the count of each occurrence of a string using the Count(Col4) function. How do I make it so that each entry will have the list of volumes in a separate column like below?

Book name	Volumes
Blue Lock	1
Naruto	9, 10
Harry Potter (novel)	2
Girl, unframed

Here is an example of what I am trying to achieve.

Answer 1

You may try:

=let(Σ,ifna(filter({regexextract(A2:A,"(.*?)(?:,|$)"),regexextract(A2:A,"(\d+)$")},J2:J="Y")),
    map(unique(index(Σ,,1)),lambda(Γ,{Γ,textjoin(", ",1,ifna(filter(index(Σ,,2),index(Σ,,1)=Γ)))})))

FORMULA_BREAKUP

step 1 (separating book name and volume number)

=ifna(filter({regexextract(A2:A,"(.*?)(?:,|$)"),regexextract(A2:A,"(\d+)$")},A2:A<>""))

step 2 (picking unique book names)

=let(Σ,ifna(filter({regexextract(A2:A,"(.*?)(?:,|$)"),regexextract(A2:A,"(\d+)$")},A2:A<>"")),unique(index(Σ,,1)))

step 3 (filtering volume numbers for each unique book and joining them)

=textjoin(", ",1,filter(S:S,R:R=T2))