Missing Dates for multiple clients

[bcx]

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.

Please Login or Register  to view this content.

Becomes...

Please Login or Register  to view this content.

So far this is all I could find, which doesn't go until the end of the month when the client changes.

Please Login or Register  to view this content.

Ultimately I want to get the average data for each month including missing dates PER CLIENT.

Any help is appreciated.

[jindon]

Assuming data in Col.A to C, header in 1st row.

Please Login or Register  to view this content.

[bcx]

This is great. Thank you so much.

The only thing is that when a client has its first date after the beginning of the month

Please Login or Register  to view this content.

then it should use the 2000 for 1/1/2013 through 1/5/2013. Right now the macro is using the previous client's data

Please Login or Register  to view this content.

[jindon]

Then you need to upload a sample workbook with the data and the result that you want.

[bcx]

Here's a sample of the output I'd like. In the list from F:H shows what I mean about using the first set of data for a client in a given month. In K:M is ultimately what I'm trying to get, which is the average of the extrapolated data by month and client. Is there a way to go directly to this without the long list? Because sometimes if my dataset is too large the macro does not work (in Office 2003).

MissingDtSample.xls

[jindon]

This should do

Please Login or Register  to view this content.

[bcx]

Thank you jindon. Actually if you look at the red coloured cells in F:H of my sample file, you'll see that those values are different from the result with your code. For example, I wanted:

Please Login or Register  to view this content.

And is there a way to not have to show the long list F:H and still do the calculation? In my version of Excel if I have too much data this part will be more than the maximum rows.

[jindon]

Explain why:

H272 = 2200, equal to one above.
H182 = 2200, equal to one below.

These dates are both beginning of the month.

[bcx]

Explain why:

H272 = 2200, equal to one above.
H182 = 2200, equal to one below.

These dates are both beginning of the month.

Because F181 != F182 (they are different clients)
While F271 = F272

[jindon]

Please Login or Register  to view this content.

[bcx]

Many thanks jindon. This is extremely helpful!