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" wrote in message ...
( 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
|