Copy Rows and Expand Dataset

Hi,

I am trying to expand my dataset by copying rows and changing one cell within each row when it is copied.

In Column M there is an ID. The ID repeats on many rows because it has data on many different dates (Column O)

In general, the dates are generally at the end of a quarter. They are usually March 31st, June 30, Sep 30, Dec. 31st. There is usually not data in between, so I need to fill the data in between the quarters. Sometimes, the date could be August, or July, or any month really.

So lets say the ID is 1 and the Date is Mar 31st. I would want to copy the row twice. One time I would copy it Feb 28th and the other time to Jan 31st.

However, sometimes there is already a row with ID 1 and Date Feb 28th. If this is the case, I would not want copy the row this time.

Also, when I speak about dates, all that really matters is the year and the month, so I can reformat the dates to make that easier.

I have attached a sample file with two tabs, Input and Output. In the output, a copied row is indicated by the green color. Please note the instance when there are NOT two green rows above a white row. This occurs because this row already appears.

Any help you guys could give me would be greatly appreciated. I am really really stuck and this would save my life. Please let me now if you could help.

Sincerely,

Michael

here is the attachment

Okay let me alter that procedure slightly.


1. Data sorted is OK. (Good)
2. Starting at the BOTTOM of the dataset we check the first column M value. should check rows even if its not at the end of a quarter, because this is not ALWAYS a requirement. sorry for the confusion
3. Check the row above if the column M value is the same and the end of month prior, check the next row above and see if it's same value and end of 2nd month prior. If yes, then move on
If either row is NOT the same value and/or month(s) prior, copy upward to get them
"Each time no more than two months are being added, we're not going backward in time indefinitely, just the current quarter." Is there anyway we could keep going until: first check M value. If its not the same don't keep going. If it is the same, check the date. Keep going until you reach that date (but don't overwrite). I am pretty sure this will entail never having more than two months, but I will need to verify my dataset. Seems better to have it this way though if its possible, to be sure.

To clarify the main goal, its basically to fill in all of the months without overwriting any data thats already there. (But not every ID will have the same start end end points. I just want to fill in the data between quarters)

Does that make sense or should I clarify?

Thanks,
Michael

I am really sorry but I have to change the instructions once again. If you have already done some work to do this, then please let me know if it is too difficult to alter at this point. I am really so sorry if this causes any inconvenience.

It should ALWAYS try and copy ONLY at most 2. There will never be an instance when it needs to copy more than 2, and it should always attempt. For instance,

Id date

1 feb 09
2 mar 09

output:

1 dec 08
1 jan 09
1 feb 09
2 jan 09
2 feb 09
2 mar 09

If it is too difficult, then we could definitely shortcut a fix, by labeling original rows with an indicator. Then we could see if how many new rows lie above each original row. Just a thought

Please tell me if my sample data set is too large.