Create Duplicate Records Based on T+7 (Recurring Row Help)

Greetings!

Apologies if this is an incredibly simple request, or has been covered before, but I have been scratching my head over this for the past 6 hours and am coming up incredibly short. I have a hunch this might require VBA but wanted to see if there might be a formula workaround for this as I see it in my head first.

Essentially, I am working with a program that can only export data that has already been created in the system, but am trying to extrapolate the data that has occurred so we can use it for forecasting.

Looking at my sample sheet, I would like to create duplicate records for every row, based on Column F, but with the caveat that the value in Column F is changed to that cell's date + 7 (to forecast for the following week).

An ideal finished product would return a result for row 23 as:

Keith Richards | Guitar | United Kingdom | Rock | High | 10/20/2020

Not sure if this is strictly necessary information, but our system exports Excel files as 97-2003 files, but I am using Office 365.

Thank you in advance!

Take a look to see if this will work.

Squeaky

Originally Posted by Squeaky

Take a look to see if this will work.

Squeaky

That will work perfectly! Thank you for the prompt assistance, Squeaky! Will mark this bad boy solved,

In the interest of full disclosure, I told my manager I was enlisting the assistance of an online forum, so I will not falsely take credit for your solution

Copy your headings from A1:F1 across into H1:M1, and then you can use this formula in H2:

=INDEX(A:A,INT((ROWS($1:1)-1)/2)+2)

Copy this across into I2:M2, and then you need to modify the formula in M2 to this:

=INDEX(F:F,INT((ROWS($1:1)-1)/2)+2) + 7*(MOD(ROWS($1:1),2)=0)

Then you can copy those formulae down as required.

Your date column did not come in as proper dates on my system, so I would need a much more complicated formula to turn those into proper dates, but see if this works for you.

Hope this helps.

Pete

Thanks for your reply as well, Pete! More than one way to skin a cat, I suppose!

Thanks for the rep. I thought you wanted the new records on alternate rows, but Squeaky's solution is much simpler. I think you could just add 7 onto his formula in F23 (i.e. =F2+7) rather than use the DATE function.

Pete