Convert columns to rows in Google Sheets

Question

I have a spreadsheet that looks like this

A              B              C              D              E
-------------------------------------------------------------------------
Mercedes       A Class        C Class        E Class        G Wagon
BMW            1 Series       3 Series       4 Series

and I wish to create a spreadsheet that looks like this

A              B
------------------------------
Merecedes       A class
Merecedes       C class
Merecedes       E class
Merecedes       G Wagon
BMW             1 Series
BMW             3 Series
BMW             4 Series

I have used the transpose function, but it doesn't create a new record for each value, but rather just rearranges the columns.

How might I achieve this?

Answer 1

For the sake of anyone who's search brought them to this old question on this perennial need; this type of rearranging is a bit easier than it used to be, with Google's addition of the FLATTEN function. Example formula:

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(A2:A&"|"&B2:E),"|"),"select * where Col2 is not null"))

From the inside out, here we work on a temporary array of makes and models,
with a symbol | concatenated between makes from A and models found in B:E,
FLATTEN then puts it all into a single column,
then SPLIT uses the temporary | characters to split it to a two-column array,
and finally, a quick QUERY lets us filter out blanks after the fact (like the row E3 would become), without having to do all the calculations twice.
Oh, and ARRAYFORMULA lets it work across the whole array of makes and models we input.