Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to reorganize - transpose columns to rows per article number
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transpose columns to rows using first columns repeated. | Excel Worksheet Functions | |||
Transpose a .csv from columns to rows | Excel Programming | |||
transpose 7 rows/7 columns | Excel Programming | |||
how do I transpose columns and rows | Excel Discussion (Misc queries) | |||
transpose 255+ columns into rows? | Excel Programming |