Hi,
I have a spreadsheet that lists Client in column A, Dates in column B, and data in column C.
However, there are dates missing and I'd like to:
- Fill in missing dates for each client including all dates until the end of the last month in the data.
- When my dates start after the beginning of the month, I'm missing the days from the beginning until then. Ideally I'd fill those in too with the data from the earliest day of the month.
The data should also take the previous date's value.
CLIENT DATE DATA
A 1/1/2013 1000
A 1/2/2013 5000
A 1/5/2013 7000
A 1/16/2013 9000
A 1/28/2013 2000
A 2/7/2013 400
A 2/9/2013 6000
A 3/5/2013 7000
B 1/6/2013 2000
B 1/28/2013 600
B 2/3/2013 200
B 2/7/2013 7000
B 3/6/2013 3000
C 1/2/2013 22000
C 4/2/2013 200
Becomes...
CLIENT DATE DATA
A 1/1/2013 1000
A 1/2/2013 5000
A 1/3/2013 5000
A 1/4/2013 5000
A 1/5/2013 7000
A 1/6/2013 7000
A 1/7/2013 7000
A 1/8/2013 7000
A 1/9/2013 7000
...
A 1/16/2013 9000
A 1/17/2013 9000
A 1/18/2013 9000
...
A 1/28/2013 2000
A 1/29/2013 2000
A 1/30/2013 2000
A 1/31/2013 2000
A 2/1/2013 2000
A 2/2/2013 2000
...
A 2/7/2013 400
A 2/8/2013 400
A 2/9/2013 6000
...
A 3/5/2013 7000
...
A 3/31/2013 7000
B 1/1/2013 2000
B 1/2/2013 2000
B 1/3/2013 2000
B 1/4/2013 2000
B 1/5/2013 2000
B 1/6/2013 2000
B 1/6/2013 2000
B 1/7/2013 2000
B 1/8/2013 2000
B 1/9/2013 2000
...
B 3/6/2013 3000
B 3/7/2013 3000
B 3/8/2013 3000
...
B 3/31/2013 3000
C 1/1/2013 22000
C 1/2/2013 22000
C 1/3/2013 22000
C 1/4/2013 22000
...
C 4/1/2013 22000
C 4/2/2013 200
...
C 4/30/2013 200
So far this is all I could find, which doesn't go until the end of the month when the client changes.
Sub AddMissingDates()
Dim i As Long: i = 2
Do
If Cells(i + 1, "B") > Cells(i, "B") + 1 Then
Rows(i + 1).Insert xlShiftDown
Cells(i + 1, "B") = Cells(i, "B") + 1
Cells(i + 1, "A") = Cells(i, "A")
Cells(i + 1, "C") = Cells(i, "C")
End If
i = i + 1
Loop Until Cells(i + 1, "B") = ""
End Sub
Ultimately I want to get the average data for each month including missing dates PER CLIENT.
Any help is appreciated.
Bookmarks