I have a table with dates (in increasing order) and corresponding values. Some dates are missing, like 3/27 below. I'd like to insert them in Date column, and have 0 entered in the Value column for these missing dates. How to achieve this, preferably with a formula that does not need to be copied down the sheet?
+---+-----------+-------+
| | A | B |
+---+-----------+-------+
| 1 | Date | Value |
| 2 | 3/25/2016 | 25 |
| 3 | 3/26/2016 | 15 |
| 4 | 3/28/2016 | 74 |
+---+-----------+-------+
Desired output:
+---+-----------+-------+
| | C | D |
+---+-----------+-------+
| 1 | Date | Value |
| 2 | 3/25/2016 | 25 |
| 3 | 3/26/2016 | 15 |
| 4 | 3/27/2016 | 0 |
| 5 | 3/28/2016 | 74 |
+---+-----------+-------+
Best Answer
I used two arrayformulas for this purpose. One, in cell C2, to create the list of dates:
It adds the row number minus 2 to the date in A2. If the result is greater than the last date in column A, this date is out of range and is not shown.
Then in column D,
vlookupis used to locate the value for each date. If it cannot find the exact date (raising an error), 0 is used.The last parameter of
vlookup, "sorted" is set to False in order to force exact match, even though the array of dates is actually sorted.