Creating an ARRAY from non-sequential periods

[James McEwan]

I am trying to consolidate similar impact assumptions year over year - to save having to create large tables of impact numbers going out 20-30 rows/columns. I was hoping I could only add when the changes occurs, please see the example below (with result I need):

Please Login or Register  to view this content.

Hope this makes sense. Thanks!

[shank_mis]

Let's assume that your dates are in Column A and Values are in Column B:

Write this formula in Column C:

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter

You will get you array. To see the array select the cell where you have written the formula ---> Go to Formula Bar ---> and Press F9