• The forum (and all other resources in Chandoo.org) will be under maintenance and will be unavailable for a maximum of two hours on the first week of October 2023. The exact date, time and duration of the maintenance are subject to change. We regret the inconvience it may cause.
  • Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Tricky VBA - Repeating data for every month between dates

Drax

New Member
Hi Guys,
I am trying to do something that is far beyond my skill set.
I have attached a link to an excel file. The file has the following sheets:

original: One row of data for every opportunity, this is my source data
desired: This is what I am trying to achieve, each opportunity repeated for every month between Onhire and Offhire dates with the month and year values added in columns H and I
fiscal calendar: This is the company fiscal calendar I use to calculate hire days

Note: All columns from J-N are just formulas and won't cause me any issues, the issue I need solved is columns A-I and the repeated data.
Link to my excel file :) https://1drv.ms/x/s!ArazqhFmvkSJlmfmETLt9IPYWVKg?e=0jsjBV
 
Drax
About Your I have attached a link to an excel file.
I would be more useful for others to attach a sample file here with [ Attach files ] -button ... links goes to somewhere.
 
In the attached, not a macro solution but a Power Query offering.
The table at cell A14 of the desired sheet is the results table and can be updated (like a Pivot table) by right-clicking it and choosing Refresh.
Do this after updating the tables Table1 and FiscalCalendar on the other sheets with new data.
I've left your expected results for comparison with the new table.
 

Attachments

  • Chandoo49197Opportunity Analysis.xlsx
    27.4 KB · Views: 3
Back
Top