Search code examples
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygs-vlookup

Conditional Transpose with join in Google Sheets

I want to create in 1st sheet (Dashboard) an author based automated booklist from my 2nd sheet (StockList) like the link below:

https://docs.google.com/spreadsheets/d/16OOzurqBchn5sx5BCUzqVDvU9fA524aK9B5lGezjTUE/edit?usp=sharing

-> If there have multiple books by an author, created one column list with commas (Example: Dashboard!B3).

-> If there have a co-author, the book/books name created also

Dashboard

StockList

Regards

Solution

  • paste in B2 cell:

    =ARRAYFORMULA(IFERROR(VLOOKUP(A3:A, 
 {SORT(UNIQUE(TRIM(TRANSPOSE(SPLIT(CONCATENATE(
 IF(IFERROR(SPLIT(StockList!C2:C, ","))<>"", "♠"&SPLIT(StockList!C2:C, ","), )),"♠"))))),
 REGEXREPLACE(TRIM(TRANSPOSE(QUERY(QUERY(QUERY(TRIM(SPLIT(TRANSPOSE(SPLIT(CONCATENATE(
 IF(IFERROR(SPLIT(StockList!C2:C, ","))<>"", 
 "♦"&StockList!B2:B&","&"♠"&SPLIT(StockList!C2:C, ","), )), "♦")), "♠")), 
 "select max(Col1) where Col1 is not null group by Col1 pivot Col2", 0), 
 "offset 1", 0),, 999^99))), ",$", )}, 2, 0)))

    0

    custom Bengali fix:

    =ARRAYFORMULA({QUERY(TRANSPOSE(QUERY(TRIM(SPLIT(TRANSPOSE(SPLIT(CONCATENATE(
 IF(IFERROR(SPLIT(A!C3:C, ","))<>"", "♦"&A!B3:B&"♠"&SPLIT(A!C3:C, ","), )), "♦")), "♠")), 
 "select max(Col1) where Col1 is not null group by Col1 pivot Col2", 0)), "select Col1", 0), 
 QUERY(QUERY(TRIM(SPLIT(TRANSPOSE(SPLIT(CONCATENATE(
 IF(IFERROR(SPLIT(A!C3:C, ","))<>"", "♦"&A!B3:B&"♠"&SPLIT(A!C3:C, ","), )), "♦")), "♠")),
 "select count(Col2) where Col2 is not null group by Col2", 0), "offset 1", 0),
 REGEXREPLACE(TRIM(TRANSPOSE(QUERY(QUERY(QUERY(TRIM(SPLIT(TRANSPOSE(SPLIT(CONCATENATE(
 IF(IFERROR(SPLIT(A!C3:C, ","))<>"", "♦"&A!B3:B&",♠"&SPLIT(A!C3:C, ","), )), "♦")), "♠")), 
 "select max(Col1) where Col1 is not null group by Col1 pivot Col2", 0), "offset 1", 0)
 ,,999^99))), ",$", )})

    0