+ Reply to Thread
Results 1 to 11 of 11

Need Help Adding Missing Date/Time Into Data

  1. #1
    Registered User
    Join Date
    09-21-2020
    Location
    Las Vegas, NV
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    5

    Need Help Adding Missing Date/Time Into Data

    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.

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Need Help Adding Missing Date/Time Into Data

    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.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    09-21-2020
    Location
    Las Vegas, NV
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    5

    Re: Need Help Adding Missing Date/Time Into Data

    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.
    Attached Files Attached Files

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Need Help Adding Missing Date/Time Into Data

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

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

  5. #5
    Registered User
    Join Date
    09-21-2020
    Location
    Las Vegas, NV
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    5

    Re: Need Help Adding Missing Date/Time Into Data

    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.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Need Help Adding Missing Date/Time Into Data

    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.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    09-21-2020
    Location
    Las Vegas, NV
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    5

    Re: Need Help Adding Missing Date/Time Into Data

    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 "

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Need Help Adding Missing Date/Time Into Data

    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.

  9. #9
    Registered User
    Join Date
    09-29-2020
    Location
    USA
    MS-Off Ver
    Ms oiffice 2017
    Posts
    5

    Re: Need Help Adding Missing Date/Time Into Data

    Thanks it was very usefull!

  10. #10
    Registered User
    Join Date
    09-21-2020
    Location
    Las Vegas, NV
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    5

    Re: Need Help Adding Missing Date/Time Into Data

    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.
    Last edited by slownickel; 09-30-2020 at 10:11 PM.

  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Need Help Adding Missing Date/Time Into Data

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 14
    Last Post: 01-08-2019, 08:16 AM
  2. Replies: 2
    Last Post: 04-10-2013, 11:17 AM
  3. [SOLVED] Adding rows for each missing date
    By kinosh1 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-19-2013, 09:36 PM
  4. [SOLVED] Adding Hours to Date + Time Text Data
    By ExcelQuestFL in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-23-2012, 01:39 PM
  5. Replies: 2
    Last Post: 04-06-2012, 02:42 PM
  6. Inserting rows for Missing Date/ Time data (Excel 2007)
    By mzalikhan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-02-2011, 08:57 PM
  7. Adding missing date rows to spreadsheet
    By mikea3 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-11-2008, 09:08 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1