# Office 365 >  >  Need Help Adding Missing Date/Time Into Data

## slownickel

I have daily, hourly, and minute data that started in 2015 and is current for 3 weather stations. They are collecting 50+ observations each. They are all missing observations at random throughout the data set independent of each other. I need to find a way to populate the missing rows with blank rows so I can line up the data sets in a new sheet. 

I have tried making helper columns with MATCH lookups, but with a data set as big as mine you find out that when you increment time it doesn't add 1 hour it ads 1.00000001 hours or whatever and none of the times match. Also when I drag down the MATCH through the column, the first three cells which I do by hand have the MATCH('CELL I AM REFERRING TOO', 'ARRAY, 0), but when I drag it down the formula in the error checker is MATCH("NUMBER EQUIVILANT TO THE TIME IN THIS CELL", "ARRAY", 0). I have played with the formatting of the cells, but I can't seem to get anything to work. If I can get that to work, I can use the MATCH function, but I figured I'd state the big picture problem in case someone has a better idea. 

Also I don't know VBA, I am starting to think learning it would be easier than trying to figure this out or doing it by hand.

----------


## JeteMc

Hello slownickel and Welcome to Excel Forum.
Not to say that I can come up with a solution, however it may be easier for one of the contributors to help if you could upload a small sample of the .xlsx file that you are working on.
Instructions for uploading an .xlsx file to the site are given in the banner at the top of the page.
Let us know if you have any questions.

----------


## slownickel

Solid, I'll upload a sample file. Let me know if you can't see it. The sample I uploaded should have a pretty big gap between 11/12 and 11/30. 


So I started going down a VBA solution 



```
Please Login or Register  to view this content.
```


I think the logic is sound, its just that I don't know how VBA quite works.

----------


## bebo021999

Last column, DV2 employed as helper to flag the gap that >1 day with "X"

=IF(INT(A3)-INT(A2)>1,"X","")

----------


## slownickel

I don't need to flag the gap, I need to insert a new line with the date in the cell. I have multiple very large files with over 40k rows, at least.

----------


## JeteMc

Note that in the attached file I left just enough data to hopefully illustrate my proposal.
To accomplish by formula, I feel the first step would be to produce a column of standardized dates and times as modeled in Column1 (column G) on Sheet2 of the attachment.
The formula used to populate Column1 is: =MROUND(A2,1/1440)
Cell A2 on Sheet1 is populated using: =MIN(practice[Column1])
Cells A3:A505 on Sheet1 are populated using: =MROUND(SUM(A2,60/1440),1/1440)
Cells B2:B505 on Sheet1 are populated using: =IFERROR(INDEX(practice[RECORD],MATCH($A2,practice[[Column1]:[Column1]],0)),"")
The remaining columns are populated using: =IF($B2="","",INDEX(practice[BattV_Avg],MATCH($A2,practice[[Column1]:[Column1]],0)))
Note that I could have populated columns C:F using the IFERROR formula utilized in column B however that requires the INDEX and MATCH functions to calculate before possibly finding the #N/A error which is not computationally efficient and I feel that this will require a lot of computing power as it is. You do have a liquid nitrogen cooled machine don't you? (HaHa).
Let us know if you have any questions.

----------


## slownickel

That definitely isn't an intuitative approach at all, at least not for someone not super familiar with Excel. Could you kind of explain the INDEX function you wrote out. I don't really understand how it works with the MATCH function. 


Also, as far as the VBA script I wrote, to get it to run, how should I proceed. 

Currently I am getting the error message 

" Runtime Error: 91
Object variable or With Block variable not set "

----------


## JeteMc

INDEX(array, row_num, [column_num])
In this case the array is the column from the practice table on Sheet2 and the row number is determined by the MATCH function.
MATCH(lookup_value, lookup_array, [match_type])
In this case the lookup_value is the timestamp in column A (Sheet1), the lookup_array comes from Column1 in the practice table (Sheet2) and the match_type is zero (exact match).
So the formula in cell B2 (Sheet1) will display the Record corresponding to the timestamp in Column1 from the practice table that exactly matches the timestamp in cell A2.
You might also gain insight as to the way the formula works by utilizing the Evaluate Formula feature on the Formulas tab.
I don't know enough about VBA to be of any help with that part.
Let us know if you have any questions.

----------


## lesliemoor1979

Thanks it was very usefull!  :Smilie:

----------


## slownickel

yea that was really good. I appreciate it. I'll update my VBA script if I can get it to work as well.

Also, when you do the reference in the INDEX function, you use the _practice[COLUMNN1]_ reference. Why is that? The sheet isn't named practice, the file is not named practice. I saved it in a directory called practice, but how is that relevant to your system.?


And then as far as populating large data sets (50k to 1,000k) rows, still dragging them down by hand?


EDIT: So I tried using the IFERROR for the entire dataset, and half of my other sets have columns out to DV. I have a gaming laptop, its an MSI with an Intel i7-8750H CPU and 16 GB of RAM. Not the greatest, but definitely solid. Didn't really seize up excel for that long. Maybe a minute.

----------


## JeteMc

The table on Sheet2 is named practice.

To save some time on copying formula for K's of rows.
Paste the column headers in row 1
Paste the formulas into cells A2:A3 and then select Find & Select > GoTo and enter the last cell you'll need to fill in the reference window (lets say A50000) and select OK.
Once the cursor is moved to cell A50000 press the Ctrl, Shift and up arrow keys which should select all cells between A3 and A50000
Now press the Ctrl and d keys which should copy the formula in cell A3 down to cell A50000
Paste the formula into cell B2 and double click the fill handle which should copy that formula down to cell B50000
Paste the formula for the rest of the columns into cell C2
Move the cursor up to cell C1 and press the Ctrl and right arrow key which should move the cursor to cell DU1
Move the cursor down to cell DU2 and press the Ctrl, Shift and left arrow keys which should select all cells between C2 and DU2
Press the Ctrl and r keys which should copy the formula from C2 across to cell DU2
While C2:DU2 are still selected double click the fill handle of cell DU2 which should copy the formula in all columns (C:DU) down to row 50000
Let us know if you have any questions.

----------

