Excel - Columns into rows

[Richard Pace]

I have been entering data from surveys into an Excel spread sheet.
Each respondent may have up to twelve types of problems that they will
have written something about. I have created a separate column for
each row.This is what it looks like (if you get my drift).


-------- ------- ----------------- -----------------------
|Id No: |Name: |Problem 1(Engine)|Problem 2(Transmission)|
-------- ------- ----------------- -----------------------
|1000 |John |Too noisy |Gears hard to change |
-------- ------- ----------------- -----------------------

(with 12 columns for the problems)
(and 200 rows for the names - so far)

But now the client wants each problem to be listed in separate rows
not columns. With the repondents name on each row if there is more
than one problem. This is what it should look like.
--------- -------- -------------- --------------------
|Id No: |Name: |Problem |Verbatim |
--------- -------- -------------- --------------------
|1000 |John |Engine |Too noisy |
--------- -------- -------------- --------------------
|1000 |John |Transimission |Gears hard to change|
--------- -------- -------------- --------------------
(etc up to 12 problems and the verbatim).

I have already entered the data for over 200 surveys. Is there an easy
way to automatically transform the data from the first format into the
second format.

I will be eternally grateful to anyone who can answer this problem.
(Assuming there is an answer).

Thank you,
Harry

[JulieD]

Hi Richard

check out my answer in microsfot.public.excel.misc -

just for future reference there's no need to post to multiple groups as most
of the responders here read a number of groups and all it does is fragment
the answers and annoy people who might spend considerable time coming up
with a solution to a question only to discover the questions been answered
in another group.

Cheers
JulieD

"Richard Pace" <[email protected]> wrote in message
news:[email protected]...
>I have been entering data from surveys into an Excel spread sheet.
> Each respondent may have up to twelve types of problems that they will
> have written something about. I have created a separate column for
> each row.This is what it looks like (if you get my drift).
>
>
> -------- ------- ----------------- -----------------------
> |Id No: |Name: |Problem 1(Engine)|Problem 2(Transmission)|
> -------- ------- ----------------- -----------------------
> |1000 |John |Too noisy |Gears hard to change |
> -------- ------- ----------------- -----------------------
>
> (with 12 columns for the problems)
> (and 200 rows for the names - so far)
>
> But now the client wants each problem to be listed in separate rows
> not columns. With the repondents name on each row if there is more
> than one problem. This is what it should look like.
> --------- -------- -------------- --------------------
> |Id No: |Name: |Problem |Verbatim |
> --------- -------- -------------- --------------------
> |1000 |John |Engine |Too noisy |
> --------- -------- -------------- --------------------
> |1000 |John |Transimission |Gears hard to change|
> --------- -------- -------------- --------------------
> (etc up to 12 problems and the verbatim).
>
> I have already entered the data for over 200 surveys. Is there an easy
> way to automatically transform the data from the first format into the
> second format.
>
> I will be eternally grateful to anyone who can answer this problem.
> (Assuming there is an answer).
>
> Thank you,
> Harry

[Max]

( The response below was given to your identical post in .misc
a couple of hours ago .. )

Here's one set-up which may work for you ..

Assume the source data (sample below) is entered
into Sheet1, cols A to N, headers in row1, data from row2 down

ID_No Name Prob1 Prob2 Prob3 ... etc (till Prob12)
1000 John JText1 JText2 JText3 ... etc (till JText12)
1001 Mary MText1 MText2 MText3 ... etc (till MText12)
1002 Pete PText1 PText2 PText3 ... etc(till PText12)
1003 Joey JoText1 JoText2 JoText3 .. etc(till JoText12)
etc

In Sheet2
------------
Put in A2:
=OFFSET(INDIRECT("Sheet1!A"&INT((ROWS($A$1:A1)-1)/12)+2),,)

Put in B2:
=OFFSET(INDIRECT("Sheet1!B"&INT((ROWS($A$1:A1)-1)/12)+2),,)

Put in C2:
=OFFSET(Sheet1!$C$1,,MOD(ROWS($A$1:A1)-1,12))

Put in D2:
=OFFSET(Sheet1!$C$2,INT((ROWS($A$1:A1)-1)/12),MOD(ROWS($A$1:A1)-1,12))

Select A2:D2, fill down until zeros appear in cols A, B and D, signalling
exhaustion of data from Sheet1

(Sheet2's set-up will prepare the "meat" for final processing in Sheet3)

In Sheet3
------------
Put the 4 labels into A1:D1, viz.: Id No, Name, Problem, Verbatim

Put in A2:

=IF(MOD(ROWS($A$1:A1)-1,13)=12,A$1,OFFSET(Sheet2!$A$2,MOD(ROWS($A$1:A1)-1,13
)+INT((ROWS($A$1:A1)-1)/13)*12,COLUMNS($A$1:A1)-1))

Copy A2 across to D2, fill down until zeros start to appear continuously in
cols A, B and D, signalling exhaustion of data from Sheet2

Sheet3 will return the final format that you want, i.e.:

Id No Name Problem Verbatim
1000 John Prob1 JText1
1000 John Prob2 JText2
1000 John Prob3 JText3
1000 John Prob4 JText4
1000 John Prob5 JText5
1000 John Prob6 JText6
1000 John Prob7 JText7
1000 John Prob8 JText8
1000 John Prob9 JText9
1000 John Prob10 JText10
1000 John Prob11 JText11
1000 John Prob12 JText12
Id No Name Problem Verbatim
1001 Mary Prob1 MText1
1001 Mary Prob2 MText2
1001 Mary Prob3 MText3
1001 Mary Prob4 MText4
etc

For a cleaner look, suppress extraneous zeros
from showing in Sheet3 via clicking:
Tools > Options > View tab > Uncheck "Zero Values" > OK
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Richard Pace]

Thank you to both of you for helping me. I apologize for the multiple
postings. I wasn't sure how likely I would be to get get a response.
In the future I will just post to one group.


"Max" <[email protected]> wrote in message news:<#[email protected]>...
> ( The response below was given to your identical post in .misc
> a couple of hours ago .. )
>
> Here's one set-up which may work for you ..
>
> Assume the source data (sample below) is entered
> into Sheet1, cols A to N, headers in row1, data from row2 down
>
> ID_No Name Prob1 Prob2 Prob3 ... etc (till Prob12)
> 1000 John JText1 JText2 JText3 ... etc (till JText12)
> 1001 Mary MText1 MText2 MText3 ... etc (till MText12)
> 1002 Pete PText1 PText2 PText3 ... etc(till PText12)
> 1003 Joey JoText1 JoText2 JoText3 .. etc(till JoText12)
> etc
>
> In Sheet2
> ------------
> Put in A2:
> =OFFSET(INDIRECT("Sheet1!A"&INT((ROWS($A$1:A1)-1)/12)+2),,)
>
> Put in B2:
> =OFFSET(INDIRECT("Sheet1!B"&INT((ROWS($A$1:A1)-1)/12)+2),,)
>
> Put in C2:
> =OFFSET(Sheet1!$C$1,,MOD(ROWS($A$1:A1)-1,12))
>
> Put in D2:
> =OFFSET(Sheet1!$C$2,INT((ROWS($A$1:A1)-1)/12),MOD(ROWS($A$1:A1)-1,12))
>
> Select A2:D2, fill down until zeros appear in cols A, B and D, signalling
> exhaustion of data from Sheet1
>
> (Sheet2's set-up will prepare the "meat" for final processing in Sheet3)
>
> In Sheet3
> ------------
> Put the 4 labels into A1:D1, viz.: Id No, Name, Problem, Verbatim
>
> Put in A2:
>
> =IF(MOD(ROWS($A$1:A1)-1,13)=12,A$1,OFFSET(Sheet2!$A$2,MOD(ROWS($A$1:A1)-1,13
> )+INT((ROWS($A$1:A1)-1)/13)*12,COLUMNS($A$1:A1)-1))
>
> Copy A2 across to D2, fill down until zeros start to appear continuously in
> cols A, B and D, signalling exhaustion of data from Sheet2
>
> Sheet3 will return the final format that you want, i.e.:
>
> Id No Name Problem Verbatim
> 1000 John Prob1 JText1
> 1000 John Prob2 JText2
> 1000 John Prob3 JText3
> 1000 John Prob4 JText4
> 1000 John Prob5 JText5
> 1000 John Prob6 JText6
> 1000 John Prob7 JText7
> 1000 John Prob8 JText8
> 1000 John Prob9 JText9
> 1000 John Prob10 JText10
> 1000 John Prob11 JText11
> 1000 John Prob12 JText12
> Id No Name Problem Verbatim
> 1001 Mary Prob1 MText1
> 1001 Mary Prob2 MText2
> 1001 Mary Prob3 MText3
> 1001 Mary Prob4 MText4
> etc
>
> For a cleaner look, suppress extraneous zeros
> from showing in Sheet3 via clicking:
> Tools > Options > View tab > Uncheck "Zero Values" > OK

[Max]

You're welcome, Richard !
But were you able to get the suggested set-up to work over there ?
(you didn't say <g>)
I've got a working sample file with the set-up implemented.
If you're interested, just post a "readable" email add here
and I'll send it via private email
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Richard Pace" <[email protected]> wrote in message
news:[email protected]...
> Thank you to both of you for helping me. I apologize for the multiple
> postings. I wasn't sure how likely I would be to get get a response.
> In the future I will just post to one group.

[Max]

> ... just post a "readable" email add here ..
or, if the email add in your post is valid,
just post a response here if you want the sample file
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----