Fill in missing data from date series in Excel

Question

I have a set of data that looks like this when imported:

I would like two transformations:

1. Missing dates filled in to ensure all dates between the start and end dates are accounted for. Note the gap between 3/5 and 3/8 as well as 3/9 also being missing)
2. The price should be set to the value of the last known price. Ex. the price from 3/5 to 3/7 would be $2,000 until 3/8 when it changes to 3,000

Final result should look like this:

What is the easiest way of accomplishing this? Is there a "native" way? Or will I have to use formulas?

Answer 1

You could try using the following single dynamic array formula, exclusively works with MS365

---

=LET(
     _Data, A1:B4,
     _Header, TAKE(_Data,1),
     _Date, DROP(TAKE(_Data,,1),1),
     _Series, SEQUENCE(MAX(_Date)-MIN(_Date)+1,,MIN(_Date)),
     _Output, HSTACK(_Series, VLOOKUP(_Series,_Data,2,1)),
     VSTACK(_Header, _Output))

---

Or For Dates:

=A2+ROW($ZZ$1:INDEX($Z:$Z,MAX($A$2:$A$4)-MIN($A$2:$A$4)+1))-1

---

For Price:

=VLOOKUP(D2#,$A$2:$B$4,2,1)

---