Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 3rd 08, 02:25 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2007
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  
Old July 3rd 08, 03:04 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
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  
Old July 3rd 08, 03:57 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2007
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  
Old July 3rd 08, 04:11 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2007
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 do i convert vertical data into horizontal data Rod Dakan Excel Discussion (Misc queries) 6 February 27th 08 06:59 PM
How to convert vertical blocked list to horizontal list? G Lykos Excel Worksheet Functions 7 December 18th 06 03:06 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 09:30 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017