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

Hi Richard

check out
http://www.j-walk.com/ss/excel/usertips/tip068.htm

for a method to achieve what you're after

Cheers
JulieD

"Richard Pace" wrote in message
om...
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

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 <atyahoo<dotcom
----
"Richard Pace" wrote in message
om...
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

Thank you very much. For the tip.


"JulieD" wrote in message ...
Hi Richard

check out
http://www.j-walk.com/ss/excel/usertips/tip068.htm

for a method to achieve what you're after

Cheers
JulieD

"Richard Pace" wrote in message
om...
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

you're welcome, please post back if you need additional assistance

"Richard Pace" wrote in message
m...
Thank you very much. For the tip.


"JulieD" wrote in message
...
Hi Richard

check out
http://www.j-walk.com/ss/excel/usertips/tip068.htm

for a method to achieve what you're after

Cheers
JulieD

"Richard Pace" wrote in message
om...
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