Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to convert vertical blocked list to horizontal list? G Lykos Excel Worksheet Functions 8 May 17th 23 07:45 PM
how do i convert vertical data into horizontal data Rod Dakan Excel Discussion (Misc queries) 6 February 27th 08 06:59 PM
Import Data From Landscape Format [email protected] New Users to Excel 0 September 11th 06 11:49 PM
cannot get excel sheet to print landscape even when set landscape williedon Setting up and Configuration of Excel 1 August 2nd 06 12:18 AM
transposing vertical data to horizontal with varying amount of data Ghosty Excel Discussion (Misc queries) 5 August 1st 06 05:20 AM


All times are GMT +1. The time now is 03:33 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"