|
How to convert data from Landscape to vertical
I have a data file with 6000 stores and 45 questions but the data came in
this way below and this is sample of four stores with 4 questions store Landscape Fruit Flower Tree -------- 12345 Y N Y N 25565 N Y Y N 35685 Y Y Y Y 25487 N N N Y I need to convert data to this format below with three columns so I can use Ms access feed Store reponsed Question 12345 Y landscape 12345 N fruit 12345 Y Flower 12345 N tree 25565 N landscape 25565 Y fruit 25565 Y Flower 25565 N tree 35685 Y landscape 35685 Y fruit 35685 Y Flower 35685 Y tree 25487 N landscape 25487 N fruit 25487 N Flower 25487 Y tree please help -- thanks |
How to convert data from Landscape to vertical
Assume your data in column A to column E, and headers in row 1
Conversion format: Headers in G1:I1 (hold Store, reponsed, and Question) In G2: =OFFSET($A$2,INT((ROWS($1:1)-1)/4),0) In H2: =OFFSET($B$2,INT((ROWS($1:1)-1)/4),MOD(ROWS($1:1)-1,4)) In I2: =OFFSET($B$1,,MOD(ROWS($1:1)-1,4)) select G2:I2 and copy down as far as needed "liem" wrote: I have a data file with 6000 stores and 45 questions but the data came in this way below and this is sample of four stores with 4 questions store Landscape Fruit Flower Tree -------- 12345 Y N Y N 25565 N Y Y N 35685 Y Y Y Y 25487 N N N Y I need to convert data to this format below with three columns so I can use Ms access feed Store reponsed Question 12345 Y landscape 12345 N fruit 12345 Y Flower 12345 N tree 25565 N landscape 25565 Y fruit 25565 Y Flower 25565 N tree 35685 Y landscape 35685 Y fruit 35685 Y Flower 35685 Y tree 25487 N landscape 25487 N fruit 25487 N Flower 25487 Y tree please help -- thanks |
How to convert data from Landscape to vertical
Very Good, if I want the result in different sheet and not in the same
worksheet begin with G2 to H2. How do i do it.? I have 45 questions so the 4 should be 45 is this true? -- thanks "Teethless mama" wrote: Assume your data in column A to column E, and headers in row 1 Conversion format: Headers in G1:I1 (hold Store, reponsed, and Question) In G2: =OFFSET($A$2,INT((ROWS($1:1)-1)/4),0) In H2: =OFFSET($B$2,INT((ROWS($1:1)-1)/4),MOD(ROWS($1:1)-1,4)) In I2: =OFFSET($B$1,,MOD(ROWS($1:1)-1,4)) select G2:I2 and copy down as far as needed "liem" wrote: I have a data file with 6000 stores and 45 questions but the data came in this way below and this is sample of four stores with 4 questions store Landscape Fruit Flower Tree -------- 12345 Y N Y N 25565 N Y Y N 35685 Y Y Y Y 25487 N N N Y I need to convert data to this format below with three columns so I can use Ms access feed Store reponsed Question 12345 Y landscape 12345 N fruit 12345 Y Flower 12345 N tree 25565 N landscape 25565 Y fruit 25565 Y Flower 25565 N tree 35685 Y landscape 35685 Y fruit 35685 Y Flower 35685 Y tree 25487 N landscape 25487 N fruit 25487 N Flower 25487 Y tree please help -- thanks |
How to convert data from Landscape to vertical
One more thing I have 6000 stores so where is 6000 in the formula or it does
matter. it only need to know 45 questions on the OFFSET functions -- thanks "liem" wrote: Very Good, if I want the result in different sheet and not in the same worksheet begin with G2 to H2. How do i do it.? I have 45 questions so the 4 should be 45 is this true? -- thanks "Teethless mama" wrote: Assume your data in column A to column E, and headers in row 1 Conversion format: Headers in G1:I1 (hold Store, reponsed, and Question) In G2: =OFFSET($A$2,INT((ROWS($1:1)-1)/4),0) In H2: =OFFSET($B$2,INT((ROWS($1:1)-1)/4),MOD(ROWS($1:1)-1,4)) In I2: =OFFSET($B$1,,MOD(ROWS($1:1)-1,4)) select G2:I2 and copy down as far as needed "liem" wrote: I have a data file with 6000 stores and 45 questions but the data came in this way below and this is sample of four stores with 4 questions store Landscape Fruit Flower Tree -------- 12345 Y N Y N 25565 N Y Y N 35685 Y Y Y Y 25487 N N N Y I need to convert data to this format below with three columns so I can use Ms access feed Store reponsed Question 12345 Y landscape 12345 N fruit 12345 Y Flower 12345 N tree 25565 N landscape 25565 Y fruit 25565 Y Flower 25565 N tree 35685 Y landscape 35685 Y fruit 35685 Y Flower 35685 Y tree 25487 N landscape 25487 N fruit 25487 N Flower 25487 Y tree please help -- thanks |
| All times are GMT +1. The time now is 02:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com