transpose not working inside arrayformula in google sheets

user_10838318 0
Viewed 100

I want to run a query to call all the comments made against the contact number and transpose them automatically throughout the column. I have tried using arrayformula but it is not working.

=ARRAYFORMULA(IF(ROW(A:A)=1,"Previous meets",IF(isblank(A:A),"",transpose(QUERY(Sheet2!A:C,"select C WHERE B="&A2:A1000&"",0)))))

link to my sheet

google-sheets
2 Answers

Try

=arrayformula(iferror({unique(Sheet2!B2:B)&"", split(trim(transpose(query(if((transpose(unique(Sheet2!B2:B))=Sheet2!B2:B)*len(Sheet2!B2:B),Sheet2!C2:C&",",),,50000))), ", ")},))

and see if that works ?

user_38894230

If you only need a list if contacts, in B2 =

=arrayformula(split(trim(transpose(query(if((transpose(A2:A10)=Sheet2!B2:B),Sheet2!C2:C&"|",),,COUNTA(Sheet2!C2:C)))),"| "))

Explanation :

  • the first step is to build a rectangular grid with by row the elements of the database Sheet2! B2: B and by column the elements we are looking for transpose(A2:A10)
  • at each intersection we are looking if the items are similar if((transpose(A2:A10)=Sheet2!B2:B), if so let's put the comment Sheet2!C2:C
  • also add a separator that we will use later, say a pipe |
  • the smartest trick now is to concatenate the results by column ... use for that the query function (not for selecting anything) and use the third (optional) parameter which is called headers, put the value at the minimum of the number of rows COUNTA(Sheet2!C2:C)) : all the results will be concatenate in a single cell by column
  • final step : transpose, split by the separator and remove the blanks corresponding to the empty cells of the rectangular grid by trim
user_150193800
Related