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.

  • What fields do you want your output to have? ID, start_date, end_date, value? – Excellll Aug 1 '12 at 20:03

To do this, you need to massage your data a bit before creating your matrix.

  1. Sort your data by ID and issue_date (A to Z) in that order or priority.
  2. Add a column end_date. The value of end_date will be the earlier of the maturity date for that record and one day before the next issue_date for that specific ID. Assuming ID is in column A, issue_date is in column B, and maturity_date is in column D, enter the following formula for the end_date of the first record. Fill down.

    =TEXT(IF(A3=A2,MIN(B3-1,D2),D2),"mmm d, yyyy")

    enter image description here

  3. On another sheet, create the framework for your matrix. Create column labels by inserting the first date and then filling over all the way to your end date. You can get your row labels by doing an advanced filter for unique records on the IDs in your table.

  4. In the top left cell of your matrix (B3 in this example), enter the following formula. 'Raw' is the name of the sheet with your original data.


    Fill over and down to fill your matrix. This will return the value of the ID at that date and 0 if there is no data available for that date.

    enter image description here

SUMPRODUCT() is one of the least intuitive but most useful functions in Excel. Basically what it does is multiply the corresponding values from each array argument and then sums up these products. Here the arrays are the cumulative values from the table and arrays of 0s and 1s that indicate whether or not a criterion has been met. Hence, only the value for which all criteria are met will have a nonzero product. Since only one record should meet all criteria, the sum will be just that value.

I'm using dates as text here, but these formulas should work for dates formatted as dates in Excel as well. The TEXT() and DATEVALUE() bits are overkill in that case, but everything should work just the same.

| improve this answer | |
  • Thanks for your answer Excellll. I definitively need the end_date suggestion you make. Some minor clarifications. I do not have duplicate IDs as rows 6-7 and rows 9-10. The output table is fine, but then I will ask yuo for advice: my initial plan was to have on columns the dates from jan1-2007 until dec 23-20111, then rows will be 1-355 (for my different ids). The FILL request implied (following your example) that ID 1 will have 900 from apr3008 to apr1209 (under each corresponding date column), then 1300 from apr1309 to apr1310, etc. So my initial output is a matrix with dates, IDs and values – Rodrigo Briceño Aug 1 '12 at 21:13
  • The advice? I wanted the matrix style output because I have a set of different values (another data set) in the same format: IDs in rows, dates in columns, who I may use to produce a quotient. Do you think that the output you suggested could be combined with this dataset in another style? Can I send you a brief set of data? it is really complicated to express the request with only text. – Rodrigo Briceño Aug 1 '12 at 21:16
  • I'm having a hard time understanding your request. It would help a lot if I could see the data. It would probably be easiest just to post a screen shot of your data and what you want the output to look like. If you can't post a picture here (because of low rep), you can post a link to the screenshots instead. – Excellll Aug 2 '12 at 14:22
  • @RodrigoBriceño Ah, with the help of some helpful edits (not mine), I think I understand what you're after now. I see that I missed the mark, but my answer gets you halfway there. I'll try to find the time to edit and give a full solution soon. – Excellll Aug 2 '12 at 19:37
  • @RodrigoBriceño I posted a new answer. – Excellll Aug 3 '12 at 15:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.