CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulatranspose
Maria
Maria

Reputation: 1

Combining rows based on IDs on 2 columns Formula in Google Sheets

I have data that looks like this:

input

And I want it to look like this:

output

Formula ideas? Thanks! Maria

Upvotes: 0

Views: 45

Answers (1)

player0
player0

Reputation: 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

Upvotes: 1

Related Questions