Need to reorganize - transpose columns to rows per article number

I have a list with five columns. Every row contains following data:
Column A contains article number. Column B-E contains info about where to find pictures.

It looks like this:
Article Info 1 Info 2 Info 3 Info 4
500086 black blue white yellow
500087 black red blue green

I would like to extract data in a different format.
I would like it to look like this:

Article Number Info
500086 1 black
500086 2 blue
500086 3 white
500086 4 yellow
500087 1 black
500087 2 red
500087 3 blue
500087 4 green

I suppose I need a macro? My list is about 1200 articles.
Note that the second column should go from 1 to 4 all over.

Thanks
Tobias

On Thursday, August 16, 2012 1:16:18 PM UTC-7, toblju wrote:
> I have a list with five columns. Every row contains following data:
>
> Article Info 1 Info 2 Info 3 Info 4
> 500086 black blue white yellow
> 500087 black red blue green
>
> I would like it to look like this:
>
> Article Number Info
> 500086 1 black
> 500086 2 blue
> 500086 3 white
> 500086 4 yellow
> 500087 1 black
> 500087 2 red
> 500087 3 blue
> 500087 4 green

With the source date in Sheet1, put the following in Sheet2.

In A2
=INDEX(Sheet1!A:A,INT((ROW()+2)/4)+1)

In B2
=MOD(ROW()-2,4)+1

In C2
=INDEX(Sheet1!$B:$E,INT((ROW()+2)/4)+1,MOD(ROW()-2,4)+1)

Copy A2:C2 down as far as needed.

Put the column headers in row 1.

Hope this helps getting started.