0

I have data that looks like this:

input

And I want it to look like this:

output

Formula ideas? Thanks! Maria

NightEye
  • 10,634
  • 2
  • 5
  • 24
Maria
  • 1
  • 3
    share a copy of your sheet – player0 Apr 09 '21 at 18:49
  • Hi @player0, I think OP means combining rows based on ids from 2 columns. A bit similar to your [solution here](https://stackoverflow.com/questions/65169246/combine-duplicate-rows-in-column-as-comma-separated-values-google-query) but added onto another column instead on the same cell. I edited the post to show what I meant. The title is misleading I believe – NightEye Apr 09 '21 at 19:32

1 Answers1

1

try:

=ARRAYFORMULA(TRIM(SPLIT(FLATTEN(QUERY(TRANSPOSE(QUERY(QUERY({A2:A&"×"&B2:B, 
 TRIM(FLATTEN(QUERY(TRANSPOSE({"×"&C2:D}),,9^9))), 
 TRIM(FLATTEN(QUERY(TRANSPOSE(C2:D),,9^9)))}, 
 "select Col1, max(Col2) where Col3 is not null group by Col1 pivot Col3"), 
 "offset 1", 0)),, 9^9)), "×")))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124