Summary table from two worksheets
 

Hi,

I need to create a summary table from two worksheets.
They are forecast budget and actual spending.
Project ID and Category are constantly updated when I get those data so that
I would like to use VBA.

First worksheet has Actual data as follow.

Project Category Quarter Amount
38852 Consulting Q2'09 2000
38852 Payroll Q1'09 1000
38852 Payroll Q2'09 4000
38852 Payroll Q3'09 2000
38852 Software Q2'09 9500
39810 Consulting Q2'09 6000
39810 Office Expenses Q2'09 500
39810 Office Expenses Q3'09 500
39810 Office Expenses Q4'09 500

Second worksheet has Forecast data as follow.

Project Category Q2'09 Q3'09 Q4'09
38852 Boards 0 0 1000
38852 Consulting 3000 0 0
38852 Payroll 3000 3000 3000
39810 Consulting 2000 2000 2000
39810 Software 1000 1000 1000

Then I would like to create a summary data as follow.

Project Category Q1'09 Q1'09 Q1'09 Q2'09 Q2'09 Q2'09 Q3'09 Q3'09
03'09....
FCast Actual Diff FCast Actual Diff
FCast Actual Diff ....
38852 Boards 0 0 0 0 1000 -10000 0 0
0
38852 Consulting 0 3000 -3000 2000 0 2000 0 0 0
38852 Payroll 0 1000 -1000 3000 4000 -1000 3000 2000
1000
38852 Software 0 0 0 0 9500 -9500 0 0 0
39810 Consulting 0 0 0 2000 6000 -4000 2000 0
2000
39810 Software 0 0 0 1000 0 1000 1000
0 1000 39810 Office 0 0 0 0 500
-500 0 500 -500
Expenses

How can I create this table?
Any help greatly appreciated.

Thanks,
Jen

Summary table from two worksheets
 

if you add a column for say "Type" which would be either "Actual" or
"Forecast", then you and simply append one table to the other and
apply/create a pivot table.

"Jen" wrote in message
...
Hi,

I need to create a summary table from two worksheets.
They are forecast budget and actual spending.
Project ID and Category are constantly updated when I get those data so
that
I would like to use VBA.

First worksheet has Actual data as follow.

Project Category Quarter Amount
38852 Consulting Q2'09 2000
38852 Payroll Q1'09 1000
38852 Payroll Q2'09 4000
38852 Payroll Q3'09 2000
38852 Software Q2'09 9500
39810 Consulting Q2'09 6000
39810 Office Expenses Q2'09 500
39810 Office Expenses Q3'09 500
39810 Office Expenses Q4'09 500

Second worksheet has Forecast data as follow.

Project Category Q2'09 Q3'09 Q4'09
38852 Boards 0 0 1000
38852 Consulting 3000 0 0
38852 Payroll 3000 3000 3000
39810 Consulting 2000 2000 2000
39810 Software 1000 1000 1000

Then I would like to create a summary data as follow.

Project Category Q1'09 Q1'09 Q1'09 Q2'09 Q2'09 Q2'09 Q3'09 Q3'09
03'09....
FCast Actual Diff FCast Actual Diff
FCast Actual Diff ....
38852 Boards 0 0 0 0 1000 -10000 0 0
0
38852 Consulting 0 3000 -3000 2000 0 2000 0 0
0
38852 Payroll 0 1000 -1000 3000 4000 -1000 3000 2000
1000
38852 Software 0 0 0 0 9500 -9500 0 0
0
39810 Consulting 0 0 0 2000 6000 -4000 2000
0
2000
39810 Software 0 0 0 1000 0 1000 1000
0 1000 39810 Office 0 0 0 0
500
-500 0 500 -500
Expenses

How can I create this table?
Any help greatly appreciated.

Thanks,
Jen