Expert: Bill Hermanson - 9/30/2008

Question
QUESTION: We get 5 minute data from machines that we operate and I need to filter the data to show every 15 minutes for State Mandated reports.  The problem is we might not have every 5 minutes.  There could be gaps.  I was manually going through, creating a pivot table, looking for all of the holes, filling those in with "dummy" data, then creating another column to number so that 00,15,30,and 45 were all 0 (using a mod formula) and filtering for 0.

Its taking me a week to do each site and I'm sure there's an easier way.  I still need to have an entry for every 15 minutes, but it would be much quicker to fill the holes every 15 minutes, then every 5.

I hope that makes sense.

Thanks!

ANSWER: Sarah,

  I have more questions than answers.

  I'm SURE there is an easier way than doing it manually!

  So if you get data every 5 minutes in the ideal case, what do you do with each set of data?  Do you take the average over the 15-minute interval?  Do you add it all up within the 15-minute interval?  Do you discard all but the data sets that are right on the 15-minute breaks (00, 15, 30, & 45?).  

  Filtering on Zero is meant to show only the values that contain zero in your added column, correct?

   So you said there 'could be gaps". Do you mean that some of the data at 00, 15, 30, and 45 is MISSING?  How do you "make up" the dummy data you mentioned?

   When you get the data from the websites, do you have problems getting it into Excel nice and neatly?  Are you spending a lot of time fussing around with the data before you can even look at it?

   If you get back to me with more detailed explanations of what your data looks like and what you actually do with it, I can probably help you save a LOT of time.

   >>> Bill

---------- FOLLOW-UP ----------

QUESTION: Hopefully I can answer them all to have this make more sense.

1)  The data I get has 5 columns (well many more, but I only need the 5).  A "reason" column (why the machine wrote that line of history - it automatically takes 5 minute "timestamps" and also any time its in a state of error) Date column, a time column, a temperature in and temperature out.  I can discard everything but the data that is on the exact 15 minute marks (00,15,30,45).

2,3 &4) The data comes from a propriety program.  We call the machines daily and download & save the history.  I look at each day's "history" within the program and export it to excel.  I found a macro to combine each daily sheet I get into 1 "master" sheet for the month.  After that I filter for "timestamp" in the reason column (discarding everything else), create another column (combined Date&Time) and filter out duplicates (overlaps from day to day)using another macro I found.  

There can be many "holes" in the data because the motherboard on the machine can only hold approx 1,000 lines of history which also includes all the times its in a state of error (I can't use those lines).  To find what's missing from the 5-minute "time stamps", I create a pivot chart with date on top, time down the left and "temp in" in the box and look for any Date&Times that don't have a 1.  Then I make up what's missing by manually writing another line in the spreadsheet with the date & time (that's missing) and "0" for temp in and temp out.  This is the part that takes the absolute longest time since I'm looking at 30/31 days at a time (of 5 minute data so theoretically I should have 8640 lines for 30 days), finding holes in the pivot table and manually entering in the missing date/times/temps.

Once I was sure I had a line for every 5 minutes during the month, I create another column and use a =mod formula so that each 15 minute marker would land on 0 and filter for 0 so that I had each 15 min.  For the formula to work though I HAVE to ensure that I have a line for every 5 minutes during the month.

If I could figure out how to remove everything but the 00,15,30,45 from the start that alone would significantly reduce the amount of time I am spending doing this.

I have to do this every quarter for 4 different machines.

Answer
Sarah,

  There is a solution to this but it is going to be very complicated to get it worked out.  I have a great solution to that issue: see the last  several paragraphs of this answer.

   Without actually seeing this data, I can only give you a general guideline as to what I would do, and it is somewhat of a guess.

    Create a sheet in the workbook which receives the data you get from the machine.  Let's call this the INPUT sheet.

   Create a big blank-looking template form which looks like the data you want to end up with.  This form should have rows for all the data that you ultimately need, that is, it should have a space for every item of data you want.  Create this as if it comes from an ideal machine which is never in error and has an infinite amount of motherboard memory.  Lets call this the IDEAL sheet.
  
   Populate the data area of this template (the IDEAL sheet) with various VLOOKUP (or other lookup and reference functions like MATCH() & INDEX-pairs) which will retrieve all the existing data from the REAL data sheet you get.  I think you will need to use TIME as the lookup value.  This data will go into the correct spaces, leaving 'holes' for the missing data.  Many of the VLOOKUPS will return NA (or perhaps some other error), since some data will be missing, but this is to be expected.

   Create another sheet that is an exact copy of the IDEAL sheet, and rename it the PROCESSED sheet.  Delete all the vlookups from the PROCESSED sheet.  Fill the PROCESSED sheet instead with ISERROR functions, themselves embedded in IF statements, to detect which values in the IDEAL sheet were not found.  The IF statements will generate values for the missing data based on if there is a VLOOKUP error, or will use the real retrieved data if there was no error.

   The PROCESSED data is what you want.  Each time you get a new machine's data, import it into the INPUT page of this worksheet.  Then COPY and PASTE-SPECIAL VALUES the results of the PROCESSED sheet to a final sheet, call it the OUTPUT sheet.  This OUTPUT sheet is the sheet you want to use for whatever you ultimately do with this data.

   I know this is going to be very difficult to make it work.  Each of the VLOOKUPS has to be different, so you can't easily create them all at once.  It is likely that there will be something special you need to do to fix the time stamp values so they are consistent with what you need, but I can't tell exactly what you need to do without a better understanding of your data.  Missing and irregular data is always a problem.  Getting the PROCESSED sheet to work will be the tricky part of this workbook, but I assure you it can be done.

   Now for my offer:  Creating spreadsheets just like this one is my exact area of expertise!  I am a professional, world-class EXPERT at fixing and re-arranging databases just like this one.  So, I suggest you hire me to do this for you, so you can get on with your regular job instead of spending about 10--20 hours to make this complex sheet work.  While you do your regular job, I'd be creating this workbook for you.  It should be easy to justify to your boss a reason to contract this work to me.  Simply figure out how long it takes you to do this job NOW, and how much time you will save using the method I will develop for you.  (I am guessing that you will only need to spend 5 minutes per machine using my method).  Then determine the number of months it will take to recover the cost of having me develop this for you vs the hours you don't need to waste any more.  I suspect that number will be very low!  Without actually seeing your data, I will GUESS that I could do this job for $750.  The actual cost may be higher or lower once I see your data.

  If you are interested in pursuing this route,  allow me to send you a powerpoint presentation about my company, even before you have talked to your boss.  My presentation will explain the various methods I have in place to ensure that you get exactly the spreadsheet product that you want, since it may be impossible for us to meet face to face (unless you are a Colorado-based company).  I have developed an excellent method for transferring your requirements to me, and a development process which allows you to review the intermediate stages of development so you are assured that I am moving along the correct path.  You can send me your email address by writing to me at EXPERTATEXCEL@AOL.COM so I can send you this presentation.

  In order for me to perform this workbook development for you, I'd have to see several samples of the raw data you get, the 'ideal' data you produce with your present cumbersome method, and any analysis or reports you generate from the results you presently get (then I would be able to include them in the product I create for you).  Once I see this information I could give you a better estimate of the cost of the project.

   If you choose to continue to develop this on your own, I will continue to advise you through the ALLEXPERTS website.  Just tell me the problems you encounter along the way and I'll advise you how to fix things so they work properly, the best I can.

   I look forward to a possible professional relationship with your company.

   >>> Bill Hermanson,

       EXCEL EXPERT, LLC.

       EXPERTATEXCEL@AOL.COM