Sort values in google sheet such that the new column will be a new row

Question

Example, I have table

Name	Value1	Value 2	Value 3
XYZ	V1	V2
ABC	V3
PQR	V5		V12

I need output as:

Name	Value
XYZ	V1
XYZ	V2
ABC	V3
PQR	V5
PQR	V12

SO i tried Transpose in google sheet, but I guess my job is close but not done yet. can you suggest me anything?

Answer 1

You need to unpivot your columns not transpose them. Something like this should work :

=QUERY(ARRAYFORMULA(SPLIT(FLATTEN(A3:A5&"&"&B2:D2&"&"&B3:D5);"&"));"select Col1,Col3 where Col3<>'' LABEL Col1 'Name',Col3 'Values'")