Need to reorganize - transpose columns to rows per article number

[toblju]

I have a list with five columns. Every row contains following data:
Column A contains article number. Column B-E contains info about wher
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
Tobia

 

[zvkmpw]

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.