A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

How to convert data from Landscape to vertical



 
 
Thread Tools Display Modes
  #1  
Old July 3rd 08, 02:25 AM posted to microsoft.public.excel.worksheet.functions
liem
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
Ads
  #2  
Old July 3rd 08, 03:04 AM posted to microsoft.public.excel.worksheet.functions
Teethless mama
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  
Old July 3rd 08, 03:57 AM posted to microsoft.public.excel.worksheet.functions
liem
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  
Old July 3rd 08, 04:11 AM posted to microsoft.public.excel.worksheet.functions
liem
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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 05:59 PM
How to convert vertical blocked list to horizontal list? G Lykos Excel Worksheet Functions 7 December 18th 06 02: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:28 PM.


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