1

Sample Sheet 1:

  A   B
1 a   x
2 a   y
3 a   z
4 b   i
5 b   h
6 b   w

Formula result, in Sheet 2, I am looking for:

  A   B   C   D
1 a   x   y   z
2 b   i   h   w
2

={UNIQUE(FILTER('Sheet 1'!A1:A, 'Sheet 1'!A1:A<>"")),
  ARRAYFORMULA(SPLIT(TRANSPOSE(
  QUERY(TRANSPOSE(IF(TRANSPOSE(
  QUERY(TRANSPOSE(
  QUERY({'Sheet 1'!A1:B}, "select count(Col1) 
                           where Col1 !='' 
                           group by Col1 
                           pivot Col2", 0)),
                          "select Col2, Col3", 0))>=1, 
  QUERY({'Sheet 1'!A1:B}, "select count(Col1) 
                           where Col1 !='' 
                           group by Col1 
                           pivot Col2 
                           limit 0"), ))&"♥", , 
  COLUMNS(TRANSPOSE(A2:B)))), "♥ "))}

  • Just curious, what the green hearts do? It seems to work without them. – Francisco Luz Feb 19 at 2:23
  • 1
    just for cuteness :) in some cases may not work without them but they can be changed for any symbol (should be unique symbol) – user0 Feb 19 at 2:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.