I have a challenge in Excel 2010. I have a list of IDs with the following variables: issue_date, cumulative value, maturity date. Each ID has different issue date and maturity date, but all of them are in the range (period of analysis) between Jan 2, 2007 and Dec 23, 2011. A short example below:
The id has 3 different values: one for Jan 2, 2007 (value: 1000); another for May 5, 2007 (value: 1500) and the last one for Dec 4, 2007 (value: 2700). What I want is for this id to have the cumulative values duplicated for the duration it was active.
So, in this case, during the period Jan 2, 2007 until May 4, 2007 the value will be (1000); then for the period May 5, 2007 until Dec 3, 2007 the value will be 1500; finally for the period between Dec 4,2007 until maturity date (let's say Mar 3, 2010) the value will remain at 2700.
This is hard challenge for me because I have 355 different ids. Some ids have unique values, meaning their cumulative value will be replicated until maturity dates. However, there are other ids that have different cumulative values (in different dates) that should be arranged properly.
The final output will be a set of consecutive dates between Jan 2, 2007 and Dec 23, 2011 (in columns) and then 355 rows of data allocated correspondingly. I can send a brief example in Excel if it is necessary to have a clearer idea of the request.