How to fill missing cells in the collumn of dates?

[Artem]

Hi,

I have large set of data. I have two columns for every variable: first
consists of dates (from 01.01.2000 to 01.01.2008) and the second column
consists of values for every date. The problem is that some dates are missed
(for example I have 01.01.2000 and next row is 05.01.2000). Is it possble to
insert row for every missed date and fill the date in the first column? I can
do it manually but I have 7 variables with different set of dates so it will
take ages to do it manually.

Thanks.

 

[Bernard Liengme]

In column A I have: A,B,blank,blank,C,blank, blank,blank,D
In G1 type =A1
In G2 type =IF(ISBLANK(A2),G1,A2)
Copy this down as far as needed
Now I have in G: A,B,B,C,C,C,C,D
I can Copy this range; move to A1 and use Edit | Paste Special , with Values
box checked.
Now A has what I want and I can delete G

I expect someone will tell how to use GoTo Special.
Try all solutions on a copy of your working file and see which you like
best.
best wishes

 

[Max]

Another play to tinker with ..

Assume data in cols A and B, real dates in A1 down, values in B1 down

In D1:
=IF(A$1+ROWS($1:1)-1>MAX(A:A),"",A$1+ROWS($1:1)-1)

In E1:
=IF(ISNA(MATCH(D1,A:A,0)),"",VLOOKUP(D1,A:B,2,0))
Select D1:E1, copy down until blanks appear, signalling exhaustion of
extract. Cols D & E should return what you're after.

 

[Dave]

Hi Aterm,
Here is a VBA solution.
It dumps all missing dates into a spare column that you select by changing
the value of MDC.
You then copy and paste those dates to the bottom of your dates column, and
do a SORT. Remember to include both your date column and your data column in
the sort!
You also need to change the values of DC and DR to match your dates
location. See the notes in the code, which will be green once pasted into a
module.
You should do this on a copy of your data first.

Sub ListDates()
Dim DC As Integer
Dim DR As Integer
Dim NM As Integer
Dim MDC As Integer
Dim MDR As Integer
DC = 1 'Change this value to your Dates Column Number (A=1, B=2 etc)
DR = 1 'Change this value to your Dates 1st row number
MDC = 2 'Change this value to any spare column number (A=1, B=2 etc)
MDR = 1
Do Until Cells(DR + 1, DC) = ""
NM = Cells(DR + 1, DC) - Cells(DR, DC) - 1
Do Until NM = 0
Cells(MDR, MDC) = Cells(DR, DC) + NM
NM = NM - 1
MDR = MDR + 1
Loop
DR = DR + 1
Loop
End Sub

Regards - Dave.

 

[Max]

Just to clarify ...

If you have this data in A1:B3 (for one variable)
29-Dec-00 1
2-Jan-01 8
4-Jan-01 7

then you'd get this desired result in D1:E7
29-Dec-00 1
30-Dec-00
31-Dec-00
1-Jan-01
2-Jan-01 8
3-Jan-01
4-Jan-01 7

If the above doesn't work, then that probably means your dates in col A
aren't real dates. You can easily convert it to real dates all at one go via
selecting col A only, then click Data > Text to Columns. Click Next>Next to
go to Step 3, check "Date", then select, say*: DMY, click Finish
*select the correct date format

P/s: You should feedback to all who have responded to you

 

[Artem]

Sorry guys, couldn't leave some feedback before because I've just read all
advices! I tried to use all 3 methods that was supplied. VBA code doesn't
work for me, may be something was wrong. I have very little expirience in VBA
so I just left this method, but thanks anyway! From 2 methods with Excel
functions I like the best method that was provided by Max. It's quite simple
for me and works fine (I've just replaced all "," in formulas with ";").

Thanks everyone for help, you saved me lots of time!!!

 

[Max]

Welcome, and thanks for feeding back.

Do take a moment to click the "Yes" button below
from where you're reading this