excel - Columns to multiple rows -

this question has answer here:

• excel macro(vba) transpose multiple columns multiple rows 2 answers

i macro convert following

name color1 color2 color3 color4 jane   blue   pink    red   teal john    red  black  green   gold      

to

name color jane blue jane pink jane red jane teal john red john black john green john gold 

i have tried using built-in transpose tool, not seem work. seems need custom script...

with data in rows 2 , 3, pick cell , enter:

=index($a$2:$a$9999,roundup(rows($1:1)/4,0)) 

next enter:

=offset($b$2,roundup(rows($1:1)/4,0)-1,mod(rows($1:1)-1,4)) 

and copy these down:

if love macros, have macro deposit , copy formulas.