2

Current Data:

9/20/2018   6
9/22/2018   3
9/23/2018   1

Desired Output:

9/20/2018   6
9/21/2018   
9/22/2018   3
9/23/2018   1

I would like to have the missing dates added to Column A while the data in Column B remains matched up with the original date. Column B Does not necessarily need to receive a Null value, it could be '0' as well.

  • 1
    This output list could be constructed on another sheet via a complete list of dates and e.g. VLOOKUP for the column B values. You could then even replace the original list with the results. You chose the tag google-sheets-custom-function though. Will you have to do this repeatedly? If actually coding a custom function, have you made a start at it? What code do you have so far? What doesn't work? See: How to Ask. – Joel Reid May 6 at 18:45
1
  • paste this into C1 cell:

    =ARRAYFORMULA({TO_DATE(ROW(
    INDIRECT("A"&DATEVALUE(MIN(A:A))&":B"&DATEVALUE(MAX(A:A))))), IFERROR(VLOOKUP(TO_DATE(ROW(
    INDIRECT("A"&DATEVALUE(MIN(A:A))&":B"&DATEVALUE(MAX(A:A))))), A:B, 2, 0))})

0

  • then select columns C:D
  • press CTRL + C
  • press DELETE
  • select A1 cell
  • press CTRL + SHIFT + V
  • This worked perfectly, for anyone else out there: if you are creating that list from another formula you will get an error, use a helper column with "=if(istext(A1),DATEVALUE(A1))" then user0's answer in the column next to it. – Travis Manning May 7 at 22:18
0

A simpler formula than the one on the other answer.

Add the following formula to C1

=ArrayFormula({
  A1:B3;
  FILTER({A1+ROW(A1:A3),IFERROR(ROW(A1:A3)/0,)},
  ISNA(MATCH(A1+ROW(A1:A3),A:A,0)))
})

In case that you have a larger source data, Replace A1:B3 and A1:A3 accordingly.

Then do the Copy / Paste as values only steps, either by using the Google Sheets menu options, the contextual menu options (right clic) or the keyboard shortcuts.

Related

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.