Columns to multiple rows

Question

I would like a macro to convert the 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 the built-in transpose tool, but that does not seem to work. It seems like I need a custom script...

What have you tried as far as macros go? There's a thousand ways to skin this cat, a starting place for helping you would be great. — Tyeler, Sep 12 '16 at 23:20
How I personally would go about this would be with a for loop nested in a for loop. One to cycle through the names, and the nested one to cycle through the colors. — Tyeler, Sep 12 '16 at 23:23
For a flexible VBA solution, here's an old answer of mine to this question: http://stackoverflow.com/a/10922351/293078 — Doug Glancy, Sep 12 '16 at 23:29
Here is a formula version: http://stackoverflow.com/questions/39419131/copy-values-from-specific-cells-in-rows-to-a-column-in-ms-excel/39419333?noredirect=1#comment66166062_39419333 — Scott Craner, Sep 12 '16 at 23:34
You can also unpivot the data with Power Query or the old way with the wizard https://www.google.com/#q=excel+unpivot&tbm=vid — Slai, Sep 12 '16 at 23:51

Gary's Student · Answer 1 · 2016-09-12T23:48:11.153

1

With data in rows 2 and 3, pick a cell and enter:

=INDEX($A$2:$A$9999,ROUNDUP(ROWS($1:1)/4,0))

Next to it enter:

=OFFSET($B$2,ROUNDUP(ROWS($1:1)/4,0)-1,MOD(ROWS($1:1)-1,4))

and copy these down:

If you really love macros, have the macro deposit and copy the formulas.

edited Sep 12 '16 at 23:48

answered Sep 12 '16 at 23:40

Gary's Student

1 Answers1