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.

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Artem" wrote in message
...
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.

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)-1MAX(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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Artem" wrote:
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.

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.

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 NextNext 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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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" wrote:

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 NextNext 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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Welcome, and thanks for feeding back.

Do take a moment to click the "Yes" button below
from where you're reading this
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Artem" wrote:
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!!!