Forum Discussion

lmelton's avatar
lmelton
Copper Contributor
Sep 09, 2020

Filling in a Table with Intermittent Dates

I am trying to fill in a table with large amounts of data. The table I am filling has an unbroken list of dates through the year, as I am tracking a building's energy usage throughout the year. However, the building I am collecting this data from experiences power outages on a frequent basis, putting holes in my source data.

 

I have been manually filtering the data to see where the holes are, and copying and pasting the data into the table (source data is spread through several different files and worksheets), but this is getting quickly tedious as my table grows larger through the months. Is there a way for me to fill in my table with data that will put 0's in dates where there is no data for me to fill in? Using the Get Data options in Excel would be preferrable.

 

I have included the table I am filling in "Collins MS Data", and the kind of data files I am getting as reference.

BI & Data Analysis
Charting
excel

10 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Sep 09, 2020

    lmelton 

    Let me clarify - do you mean you'd like to generate the table as in Oak Hill Data sheet from the bunch of added from time to time files like Wing XXX Load, syncing resulting data by datetime?

    • lmelton's avatar
      lmelton
      Copper Contributor
      Sep 09, 2020

      SergeiBaklan 

      That is correct. The full monthly calendar in Oak Hill Data would be populated according to the incomplete dates in the data sheets, hopefully.

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Sep 09, 2020

        lmelton 

        I see, thank you. Format/structure of the source files is exactly the same as in sheets, or you modified them somehow?

  • mathetes's avatar
    mathetes
    Gold Contributor
    Sep 09, 2020

    lmelton 

     

    Based on what I see in the existing sheets, it's clear that you're not a novice with Excel. So let me just ask whether you're familiar with the newest Dynamic Array functions? Notably FILTER, which might well allow you to do the filtering of the data from your source file, bringing the desired data into your various School summary sheets.

     

    That function does require the newest version of Excel, so if you don't currently have it, it would be worth while getting it.

     

    Here's a YouTube video that serves as a good introduction to FILTER, but also some of the associated concepts and functions. https://www.youtube.com/watch?v=9I9DtFOVPIg

Resources