List of dates, need to insert missing days

[bartsimpson]

I have a list of data in Excel, which is sorted in date order :

1/1/04 a
2/1/04 b
2/1/04 f
3/1/04 c
4/1/04 d
7/1/04 a

etc

As you see, there are some dates missing - does anyone know a quick way of inserting rows into the data with a date there so I get

1/1/04 a
2/1/04 b
2/1/04 f
3/1/04 c
4/1/04 d
5/1/04 X
6/1/04 X
7/1/04 a

etc

ie - a continuous column of dates

 

[xlbo]

not without vba....

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[dhulbert]

Do it in reverse, create a list of all of the dates you require then use a combination of IF and VLOOKUP to populate the second column with either the correct bvalue or and x if there is no correct value.

 

[bartsimpson]

Thanks, both. I don't have alist of the missing dates - they are randomly spaced within the data. I just want to make sure that a have a complete list of dates ...

 

[dhulbert]

Presumably when you pulled the data you used a date select ie "between 01/01/2004 and 31/12/2004",
you can use this date range as the basis for the list then use the lookup formula to populate the rows where you have data and to flag the rows where you don't

 

[bartsimpson]

I have now cracked the problem, but used Access. I created a new table with all of the dates I needed in it, and then did a right inner join from the data table. This gave me a query result with all the dates I needed.

 

[TonyJollans]

Hi bartsimpson,

That seems like overkill!

- Let's say you have your list of dates in column A, rows 1 to 100.
- Select column A, rows 101 to, say, 500 - just make sure you select at least enough cells to hold all the dates between the date in A1 and the date in A100
- In the formula box, type:
[blue][tt]=IF(ISNA(VLOOKUP(A1+ROW(INDIRECT("1:"&A100-A1+1))-1,A1:A100,1,FALSE)),A1+ROW(INDIRECT("1:"&A100-A1+1))-1,"")[/tt][/blue]
- Press Ctrl+Shift+Enter to array-enter the formula
- Press Ctrl+C to copy
- Select Edit > Paste Special from the menu
- Choose Values and press OK
- Sort on column A
- Delete the blanks and N/As from the end
- Select Column B
- Press F5 (Edit > GoTo) and then Click on Special
- Choose Blanks and Press OK
- Type x in the formula box
- Press Ctrl+Enter

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[