Search code examples
exceldatabasegoogle-sheetstransposeflatten

Transpose data from CSV

I have an CSV with this pattern:

Column 1, Column 2, Column 3,  
NAME 1, APP1, APP2 
NAME 2, APP2 
NAME 3, APP1, APP3

I would like to transpose this data and get this result

Column 1, Column 2, Column 3,  
NAME 1, APP1
NAME 1, APP2 
NAME 2, APP2 
NAME 3, APP1
NAME 3, APP3

I would like to do it on excel or google sheets.

Any idea?

Solution

  • try:

    =INDEX(QUERY(SPLIT(FLATTEN(A2:A&"×"&B2:C), "×"), "where Col2 is not null", ))