Sorting information in Google Sheets

Question

I have some information in Google Sheets in the following format:

Authors	Number
Akwaeke Emezi	2
Ezra Claytan Daniels, Ben Passmore	3
Nick Tapalansky, Kate Glasheen	9
John Allison, Lissa Treiman, Whitney Cogar	1
John Allison, Lissa Treiman, Max Sarin, Whitney Cogar	2
Gene Luen Yang, Sonny Liew	43
Gene Luen Yang	9

I would like to have a second sheet that does the following:

1. Generate a list of each individual author in "Authors" (ideally in alphabetical order by last name)
2. Calculate the average Number associated with each author (e.g. Akwaeke Emezi would be 2 and Gene Luen Yang would be (43+9)/2)

I can do this as a one-off calculation, but what I'm really looking for is a way to set this up such that when I add more entries to the spreadsheet (where the new entries will contain a combination of new authors and authors that already appear in the list), this second sheet will automatically update the list of individual authors and their associated average numbers.

Answer 1

try:

=ARRAYFORMULA(QUERY(QUERY(SPLIT(FLATTEN(REGEXEXTRACT(
 SPLIT(A2:A, ","), " (.*)$")&"×"&TRIM(SPLIT(A2:A, ","))&"×"&B2:B), "×"), 
 "select Col1,Col2,sum(Col3)/count(Col2) where Col3>0 group by Col1,Col2"), 
 "select Col2,Col3 offset 1", 0))

or:

=ARRAYFORMULA(QUERY(QUERY(SPLIT(FLATTEN(REGEXEXTRACT(
 SPLIT(A2:A, ","), " (\w+)$")&"×"&TRIM(SPLIT(A2:A, ","))&"×"&B2:B), "×"), 
 "select Col1,Col2,sum(Col3)/count(Col2) where Col3>0 group by Col1,Col2"), 
 "select Col2,Col3 offset 1", 0))

---

update:

=ARRAYFORMULA(QUERY(QUERY(SPLIT(FLATTEN(REGEXEXTRACT(
 TRIM(SPLIT(REGEXREPLACE(A2:A, 
 "\b(?:X)?(?:X)?(?:X)?(?:V)?(?:I)?(?:I)?(?:I)?(?:V)?(?:X)?(?:X)?(?:X)?\b", ), ",")), 
 " (\w+)$")&"×"&TRIM(SPLIT(A2:A, ","))&"×"&B2:B), "×"), 
 "select Col1,Col2,sum(Col3)/count(Col2) where Col3>0 group by Col1,Col2"), 
 "select Col2,Col3 offset 1", 0))