average previous data to create a new row

[phoeberunner]

Hi there,

I have the following table in sheet1
Year Sales R1 R2 R3
2008 20 NE A B
2009 30 NE A B
2010 20 NE A B
2011 10 NE A B
2012 50 NE A B
2008 300 HJ C W
2009 200 HJ C W
2010 100 HJ C W
2012 150 HJ C W
2013 120 HJ C W

Using the above data, I would like to create a new sheet contains data from 2009-2014 (data for 2008 is not shown). The new year 2014 will take the average of 2009-2013 for respective items. The new sheet should look like below,

Please note that the table above doesn't have data of R1=NE, R2=A, R3=B for 2013, so the data for 2014 is the average of 2009 to 2012.

Year Sales R1 R2 R3
2008 20 NE A B
2009 30 NE A B
2010 20 NE A B
2011 10 NE A B
2012 50 NE A B
2014 26 NE A B
2008 300 HJ C W
2009 200 HJ C W
2011 600 HJ C W
2010 100 HJ C W
2012 150 HJ C W
2013 120 HJ C W
2014 245 HJ C W


Could someone advise an efficient way to do this? After auto filter out year not equal 2008, should I loop through all my data (1500 records) to generate data for year 2015?

Thanks!

[nimrod]

Have you looked at using a Pivot Table ?

[phoeberunner]

Have you looked at using a Pivot Table ?

VBA script is requested for this problem. Please help...

Regards