I have some data that shows how college students are doing over 4 years.
However, when I try to get cohort data, I am finding it difficult to extract
the data I require. I hope that this question makes sense and someone can
help me.

The data is organized this way:

Table 1
2000 2001 2002 2003
Fresh 78 78.2 81 78.5
Soph 68 70 70.7 79
Jun 73 54 70 65.5
Senior 77 73 54 69


In order to graph the cohort data I would like the data this way:

Table 2

Fresh Soph Jun Senior
2000 78 70 70 69
2001 78.2 70.7 65.5
2002 81 79
2003 78.5

The data has a longer string but I hope that you get the gist of what I am
doing.

I have Table 1 but don't have Table 2. Can someone please tell me how I can
get Table 2 without having to manually type in Table 2 "=B2" etc. I tried to
use the Index function but no dice. Any help would be greatly appreciated.
Thank you.

to transpose a table you can copy and then paste special using the transpose
option. I don't think your table 2 is organized properly. I would tthink a
senior would have 4 years of data and a freshman would only have 1 year of
data. The 1 year of data would be for 2003, not 2002 as shown for the senior.

"Elaine" wrote:

I have some data that shows how college students are doing over 4 years.
However, when I try to get cohort data, I am finding it difficult to extract
the data I require. I hope that this question makes sense and someone can
help me.

The data is organized this way:

Table 1
2000 2001 2002 2003
Fresh 78 78.2 81 78.5
Soph 68 70 70.7 79
Jun 73 54 70 65.5
Senior 77 73 54 69


In order to graph the cohort data I would like the data this way:

Table 2

Fresh Soph Jun Senior
2000 78 70 70 69
2001 78.2 70.7 65.5
2002 81 79
2003 78.5

The data has a longer string but I hope that you get the gist of what I am
doing.

I have Table 1 but don't have Table 2. Can someone please tell me how I can
get Table 2 without having to manually type in Table 2 "=B2" etc. I tried to
use the Index function but no dice. Any help would be greatly appreciated.
Thank you.

I have Table 1 but don't have Table 2. Can someone please tell me how I can
get Table 2 without having to manually type in Table 2 "=B2" etc. I tried to
use the Index function but no dice. Any help would be greatly appreciated.
Thank you.

I don't know if the example you give is the actual data size, or a
simplified example, but try this.

highlight the entire source table.
copy
move to a new location
pastespecial....values....transpose

The new table should look close to the sample output that you gave,
but you will need to move the whole of column 2 up by one row, the
whole of column 3 up by 2 rows, etc...

HTH

Andrew

One way if you need the transpose to be dynamic to source
Assuming source table is in Sheet1's A1:E5
In another sheet,
Place in A1: =OFFSET(Sheet1!$A$1,COLUMN()-1,ROW()-1)
Copy A1 across / fill down to E5 to return the required transpose
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Elaine" wrote:
I have some data that shows how college students are doing over 4 years.
However, when I try to get cohort data, I am finding it difficult to extract
the data I require. I hope that this question makes sense and someone can
help me.

The data is organized this way:

Table 1
2000 2001 2002 2003
Fresh 78 78.2 81 78.5
Soph 68 70 70.7 79
Jun 73 54 70 65.5
Senior 77 73 54 69


In order to graph the cohort data I would like the data this way:

Table 2

Fresh Soph Jun Senior
2000 78 70 70 69
2001 78.2 70.7 65.5
2002 81 79
2003 78.5

The data has a longer string but I hope that you get the gist of what I am
doing.

I have Table 1 but don't have Table 2. Can someone please tell me how I can
get Table 2 without having to manually type in Table 2 "=B2" etc. I tried to
use the Index function but no dice. Any help would be greatly appreciated.
Thank you.