Find and add missing dates. Autofill the newly added rows. Save as csv(comma delimited)

Hi,
When I upload information from our system, week-ends and public holidays are not included in the excel sheet. The reason being that we do not work week-ends and public holidays. However, I would need to have them included in my excel sheet.

This is how it comes out.

Date Profit Profit to date
1/01/2000 10 10
2/01/2000 10 20
3/01/2000 10 30
4/01/2000 10 40
5/01/2000 10 50
8/01/2000 10 60
9/01/2000 20 80
10/01/2000 20 100
12/01/2000 10 110
16/01/2000 30 140
17/01/2000 10 150
18/01/2000 10 160


his is how I want it to become. I would also need it in the csv (comma delimited) format. Since we are close, then the profit would be 0 while the profit to date would be the same as the previous row cell.

Date Profit Profit to date
1/01/2000 10 10
2/01/2000 10 20
3/01/2000 10 30
4/01/2000 10 40
5/01/2000 10 50
6/01/2000 0 50
7/01/2000 0 50
8/01/2000 10 60
9/01/2000 20 80
10/01/2000 20 100
11/01/2000 0 100
12/01/2000 10 110
13/01/2000 0 110
14/01/2000 0 110
15/01/2000 0 110
16/01/2000 30 140
17/01/2000 10 150
18/01/2000 10 160

The same data is in the attached excel sheet.


Also, I will upload of at least 10 years data. Hence it will be quite a long file of around 2000-4000 rows.

Thanks for your help.

Hi, thank you for your reply. It seems however that it is going through an infinite loop. I have tried to play with it based on what you wrote but I could not get it working. Excel debug is pointing at line: Cells(v, "A") = Cells(v + 1, 1) - 1

Also, your assumption is correct. My data starts from row 2.

Thanks.
-M

Hi again,

I have changed the v = v + 1 line to v = v - 1.

It is adding the dates. However, to be able to make a complete list, I need to run the macro several times. It only adds one day at a time. Since my data is for over 10 years there is a chance of the public holidays on a Friday then weekend and another public holiday on Monday, hence 4 missing lines at once.

Thank you.

Hi, you are right! There were duplicates in my sheet. I just included a remove duplicate command first and ran your commands. Works perfectly!

Thanks a LOT.

@ msls09

Welcome to the forum.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.