How to create crosstabs in a table?????

[Jac]

Hi,

Originally, I have the table of data organized in the format as below:-

Discipline Title Status Year Amount Core
Mekong D Rejected 2006 1600 1800
Mekong D Rejected 2008 2000 2500
Mekong F Rejected 2008 2600 3000
Mekong E Under Dev. 2007 3000 3200
Mekong E Under Dev. 2008 2000 2000
Pacific C Expected 2008 2000 2100
Pacific C Expected 2009 2000 2500
Pacific A Secured 2006 1000 1200
Pacific A Secured 2007 1800 2000
Pacific B Under Dev. 2007 3000 3200

But I would like the table to be re-format into the layout as below:-

Discipline Title Status 2006 2007 2008
2009
Mekong D Rejected 1600 1800 2000 2500
Mekong F Rejected 2600 3000
Mekong E Under Dev. 3000 3200 2000 2000
Pacific C Expected 2000 2100 2000 2500
Pacific A Secured 1000 1200 1800 2000
Pacific B Under Dev. 3000 3200

How would I have to convert the table format as the above one????
Do I need an array statement to do so???
By the time being, I still couldn't find a way out but I had tried out using
the array; unfortunately, thing still doesn't work out as intented!!!

Help and advice needed here..........
Thanking in advance!!!

 

[Debra Dalgleish]

You could use a pivot table to summarize the data.

Put the Discipline, Title and Status fields in the Row area, put Year in
the Column area, and Amount and Core in the Data area.

There are pivot table instructions and links here:

http://www.contextures.com/xlPivot01.html

 

[Jac]

Thank you very much for the help!! : )

You could use a pivot table to summarize the data.

Put the Discipline, Title and Status fields in the Row area, put Year in
the Column area, and Amount and Core in the Data area.

There are pivot table instructions and links here:

http://www.contextures.com/xlPivot01.html