Help moving data from rows to columns

PapaSmurfHUKD

New Member
Joined
Mar 24, 2011
Messages
2
Hi

Really stuck with this one - wondering if anyone can help

I've got a data sheet that looks a little something like this:

1 2 3 4 5........31
Mary Q1. a e i m q......
Q2. b f j n r......
Q3. c g k o s.....
Q4. d h l p t......

1 2 3 4 5........31
John Q1. a e i m q......
Q2. b f j n r......
Q3. c g k o s.....
Q4. d h l p t......

1 2 3 4 5........31
Jack Q1. a e i m q......
Q2. b f j n r......
Q3. c g k o s.....
Q4. d h l p t......

etc etc etc

...and i am looking to move the data into a Pivot Table. Now, from what little i know of Pivot Tables, the data needs to be in this format

1 Q1 Mary a
1 Q2 Mary b
1 Q3 Mary c
1 Q4 Mary d
2 Q1 Mary e
2 Q2 Mary f
2 Q3 Mary g
2 Q4 Mary h
3 Q1 Mary i
3 Q2 Mary j
3 Q3 Mary k
3 Q4 Mary l.........
1 Q1 John a
1 Q2 John b
1 Q3 John c
1 Q4 John d
2 Q1 John e
2 Q2 John f
2 Q3 John g
2 Q4 John h
3 Q1 John i
3 Q2 John j
3 Q3 John k
3 Q4 John l

as i want the pivot table to produce a graph showing trend movement in each question by day.

Any idea how to (other than cut and paste) i can get the data from one format to the other?

Please and thank you!
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

PapaSmurfHUKD

New Member
Joined
Mar 24, 2011
Messages
2


http://img593.imageshack.us/i/excelhelp.jpg/

if this helps any

where D2, D3, D4, D5 should equal H5, H6, H7, H8
and D6, D7, D8, D9 should equal I5, I6, I7, I8
and D10, D11, D12, D13 should equal J5, J6, J7, J8

etc etc
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,924
Messages
5,525,667
Members
409,658
Latest member
Yardcell

This Week's Hot Topics

Top