This was the final week before my set end date of March 28. As such, I had a few loose ends to wrap up, mostly with the documentation. The main thing I had to deal with this week was the pull data macro. This is the macro that opens each device’s spreadsheet and automatically copies and pastes the data into the master spreadsheet. After reviewing this problem in more detail, I found that the best way to handle it was to just have the user do that part. I was hoping to avoid having the user do this part since the user could incorrectly copy and paste the data into the wrong worksheet in the master spreadsheet. This would lead to major problems as you would then think a certain device registered certain points when in reality it was another device instead. Even with that though, I found that it was the best way to go, especially when this week, I ran into another problem with the pull data macro.

This week a staff member of Pam’s ran through the data downloading procedures and she informed me of the results. I was glad when she told me the documentation was easy to follow and that she was able to download the data. However, she did tell me something interesting. One of the devices registered no data points for some reason. So, she was only able to create five of the six spreadsheets. This is a problem for the pull data macro. The macro was recorded to open all six files in order. If one file does not exist, the whole macro cannot run at all. Also, if the name of one of the saved files from the data download was incorrect and did not match what is in the macro, the macro would again not run at all. So, the first thing I wanted to do was see what happened when a device did not have any data. I wanted to see why it was not possible to at least create an empty sixth spreadsheet so that the macro could still run.

To do this, I needed to redo the downloading procedures myself to see what happened. The HOBO shuttle keeps the data until a new set of data from a HOBO device is loaded off onto it. The shuttle will keep the previous months data until a new month is run. This is good in case something happens to the spreadsheets on the computer. All you would need to do to get the spreadsheets back would be to redo the procedures by simply plugging in the shuttle. Since the data was still on the HOBO shuttle, I was able to redo the procedures myself so that I could see what happened when the procedure came to the device that had no data points. After pulling the data off the shuttle as normal, I skipped straight ahead to the device that had no data (it was device L6 in Pam’s office). I tried to export the empty device’s data to a spreadsheet, but to no avail. BoxCar Pro informed me that it would not create the spreadsheet since the device had no data. So for every device that does not have data, the only way to get the file for the macro run would be to create it yourself. This was something I did not want the user to have to do as it would increase the probability of incorrectly naming the file, thus making the macro far more confusing for the user. Needless to say, this was pretty much the last straw with the pull data macro.

Although I wanted full automation, the pull data macro just created too many problems. If the files were incorrectly named or were not there, the macro would not run. If a device had no data, you would have to create the spreadsheet yourself. Also, if for some reason you needed to move all of the data to a new directory, you would have to recreate the macro. Due to all of this, I decided it would be better to have the user just copy the data out themselves and paste it into the master spreadsheet. From there the user could still run the rest of the master macro and most of the procedures would still be automated. With that decided, I had to start creating the documentation for this procedure. I also now had to edit the downloading procedures to let the user know what happens when a device does not have any data.

Before I could begin writing up the procedures, I still had to figure out why the sixth device did not collect any data points. First, I plugged the device directly into the computer and read it out using BoxCar. BoxCar picked up the device so it was indeed still functioning. While doing this, I noticed that BoxCar does not allow for apostrophes in the device name. Because of this, it was saving the spreadsheet file as Pam_s office instead of Pam’s. I went ahead and changed the name of the device to just Pams Office. While doing this, I remembered that I still needed to write the documentation on how to change the name of a device. So I started over, this time making sure to write down the necessary steps involved with changing the name of a device. Once completed, I wrote up the documentation on how to change both the name of the device and the interval (how often the device collects data points). With that documentation in place, the user could now change the interval and the name of the device should the need arise.

After completing that documentation, I still had to figure out what was wrong and why there was no data.  I was stumped. The device seemed to be working. I set the delay time on the device and put it back to its location. The delay time is merely a date and time you want the device to start collecting data points. I set the delay time for five minutes after I was finished. This would cause the device to hold off on collecting data points until that delay time hit. After putting the device back to its location, I still had no idea what the problem was so I decided to move on.

It was only until just now that I realized what may have happened. Pam had mentioned to me that her staff member told her that the shuttle at one point became full. At the time it did not hit me. I just assumed it was full because of the last months run. I thought I was just going to have to write a procedure to clear the shuttle each time. But then I realized that the shuttle is supposed to clear itself after a new HOBO device is loaded onto it. This means that once her staff member transferred the first device over, the shuttle should have been cleared and room should have been made for the new devices. This makes sense as when I ran it myself in February there were no remnants of January’s data.

I began by first looking at how to clear the shuttle. I found out that there was no manual way of clearing it. The only way to clear it is to transfer new data from a HOBO device to the shuttle. So I was still not sure what to do with this problem. Just now though, I think I realized what may have happened, and I do emphasize “may.” I have not talked to Pam or her staff member to confirm this. Her staff member may have transferred data from only five of the devices. If she went in order, Pam’s office would be the last device. When she got to the sixth device, the HOBO shuttle said it was full. From there she may have followed the procedures with just five of the devices. However, I am not sure what happened to the sixth device’s data. When I went into that device alone, there was no data. If the shuttle was full, I would hope it would just leave the data on the device. However, the shuttle did still recognize the sixth device as it had the devices name and even made the .dtf file for it when her staff member ran the procedure. So it did pick it up at some point, it just had no data points. Perhaps she tried to transfer the sixth and final device and the Shuttle was full, but not full enough to register that the sixth device was being transferred. Since maybe it was full, the device simply just lost its data. I hope this is not the case and that the shuttle does have some kind of a failsafe for this.

I will need to investigate this further.  I need to talk to the staff member and see what she did and what really happened with the full shuttle. I never expected the shuttle to be a problem. However, this was the first time we ran six full devices. So, I am going to have to take a look at this when they run the data set in April. I could have them run one now, but that won’t include a full month’s data set and as such may not fill up the shuttle.

Now, back to writing up the final documentation. I began writing up the procedure for creating the final results spreadsheet. I wrote up the documentation that made the user open all of the device’s spreadsheets. I made sure to note that there should be six files, but if a device had no data, there would be no spreadsheet. If a spreadsheet was missing, the user would just skip copying over anything to the corresponding sheet in the master spreadsheet. I then showed them how to copy and paste the data into the master spreadsheet. I was sure to make this part very descriptive and detailed so that there would be less chance for error. From there, the documentation told the user to just run the master macro. Once that is run, all they have to do is copy out the final results sheet to a new spreadsheet and then save that into the current month’s folder. For example, I went ahead and created the Mar08-results spreadsheet for the month. Every month, the user will run this procedure after the data download and they will create this new results spreadsheet. This spreadsheet shows each device and its out-of-range points all on one sheet. From there the user would then have to run the clear master macro which would clear the master spreadsheet of all data. With the master spreadsheet cleared, the user can copy and paste it into next month’s data folder when the user runs the procedure the following month.

I touched up and finished all documentation and saved them to the network drive in a documentation folder. I am confident in all the documentation I wrote up and I hope the new documentation for the results spreadsheet and how to change a device’s name are easy to follow. With the documents completed, I emailed them to Pam to let her know that I had completed all documentation.

As promised, I delivered all documentation and a working out-of-range algorithm by March 28. I do have to say that the out-of-range algorithm was not as easy as expected. I ran into a few more problems along the way then originally anticipated. I will not go into detail about what I learned from this project at this time as that is a specific assignment that is due two weeks from now. This assignment will be a final post that goes over what I learned from this project.

Even though I have completed the objectives of this project, I am still going to make sure that all is well when they run the data next month. I want to see what happens with the shuttle and what happens with the sixth device. Also, on April 17^th, I have a presentation to give to Pam and the course instructor Melissa Raulston where I will go over what was accomplished in the project among other things. After this presentation, Pam will review my performance and will fill out the sponsor evaluation form. This form is where Pam will have the opportunity to grade me on such things as professionalism, and how well she felt I met the objectives I set forth in the project plan. Once she submits the form, the project will be officially complete.

With that said, next week’s post should be really quiet. Next week, I will showcase the final project plan, which is due tomorrow for the class. I will also showcase some of the macros I created to show you how the master macro actually works. The master macro itself is nothing major as all it does is run a series of other macros. So I will be focusing more on the other macros. Until then, thanks for reading.

I bring good tidings. Upon my return from Spring Break, as promised, I have completed the algorithm. I have also developed a macro that honestly exceeds even my original expectations. As I mentioned in my last post, I solved the problem with the out of range algorithm (see the 3/10 Update in the last post). From there, I had to begin testing some data, making sure to test the algorithm for every possible scenario. First, I needed to test the algorithm to see if it correctly pulled out the data points that had just temperatures above 70 degrees Fahrenheit. I then needed to make sure that it pulled out the data points that had just relative humidity above 55%. Finally, I needed to ensure that it pulled out the data points that had both high temperature and high relative humidity.

I began testing with some of the data from February. As mentioned in the 3/10 update, at first, I didn’t feel like the algorithm was working properly as there were no points with high RH. However, after further review, I noticed that it was correct because there were actually no data points with high RH (in the data I was testing anyway). So to test the algorithm properly, I had to make up a few data points. I created data points that had just high RH and then a few that had both high RH and high temperature. After adding those points in, I ran the out of range macro and took a look at the results. From what I could see, the algorithm seemed to be working perfectly. It pulled out the ones with just high temperatures. It also pulled out the data points I created, ensuring me that the algorithm was functioning as anticipated. I was excited to see the algorithm working as intended. Next, I tested it with all of the data points from each of the devices to see what the results were. As before, it pulled out the correct out of range points for each device. With each data set tested, I was confident in the algorithm and I was ready to move on.

With the out of range algorithm in place and looking good, I had to start working on a procedure that would get the data from the device spreadsheets into the master spreadsheet. As mentioned in my last post, I determined that it was unnecessary for the user to have to input anything. All they need to do is copy the data from each device’s spreadsheet and paste it into the separate sheets in the master spreadsheet. From there, I can make the macro run for each sheet, thus avoiding the user from having to enter anything in. As such, errors should be at a minimum and the data points should stay connected to their respective devices.

With that in mind, I had to figure out the best way to approach this. The first thing I did was copy all of the data from each device and paste it into the individual worksheets in the master spreadsheet. I then recorded a macro that automatically clears the data. My plan is to use this master spreadsheet either in the individual monthly folders, or as one main spreadsheet in the root directory. As such, I created a small, quick macro that cleans all of the data out of the six worksheets, allowing it to be used again next time. With the clear data macro up and running, I had to get the data back in the master spreadsheet. This time however, I wanted to see if there was a way that I could create a macro that would automatically open the files and copy the data in. I had never attempted this in any macro I had created before, so I was not sure if it was a possibility. After I ran some quick tests, I realized that it was definitely possible. This was the moment where I began to realize that I may very well be able to record a single macro that would automate the entire process. Needless to say, I was stoked. I originally assumed I was going to have to make a few macros and have a few steps in between. However, after seeing you could have the macro open and take out the data, I determined the user may very well just be able to run a single macro. In excitement, I continued on.

I completed the macro that opened up each individual device spreadsheet. I had the macro open up each spreadsheet and copy the data to the corresponding worksheet in the master spreadsheet. After recording the macro, I opened it in the visual basic editor to see what it looked like. This is where I noticed a potential problem that I feared when recording it. To open each spreadsheet, it had to set the directory to where each file was. In the macro, it opened the directory for February 2008 so it could open the files. So every time this macro would run, it would automatically open the February 2008 folder, regardless if you wanted it to or not. This is an issue as when you go to run the macro for March, April and so on, it would automatically open February’s folder.

At this time I am still working on the best way to fix this. Worse case, I will have to make the user go into the macro and change the folder path to the monthly folder they are working with. However, that could be rather complex and it is something I would prefer to avoid since a single mistype could stop the macro from functioning properly. So I will need to find a way to have the macro realize that the master spreadsheet is in the same folder with the rest of the device spreadsheets. This way all the user has to do is put the master spreadsheet in the monthly folder they just created and it can just open up the files regardless of which month. However, this pretty much eliminates the single master spreadsheet idea. There would need to be a master spreadsheet put into each new month.

I also determined another possible problem. If the files are not named exactly as they are in the macro, or are not in the folder, it will not run correctly. So if the name of device has to change later on, or the user changes the name of the device spreadsheet by accident, the macro will simply stop and cannot be run to completion. This is just one of the problems that can occur when trying to create complete automation. To get complete automation, I have to be aware that automation comes with the higher potential for problems. However, I feel complete automation is the best way to go. In the end, I am still trying to figure out how to correct this macro. There are a few more things I think I can test to see if they can help solve this problem.

With that said, I decided to move on and start wrapping up the whole process. Once the macro opened up all of the files and copied the data in, I recorded a macro that sorts each worksheet’s data by temperature and then RH, both descending. Then I recorded another macro that runs the out of range algorithm for each sheet. This took out all of the out of range points for each sheet. Then I recorded yet another macro that sorts the out of range points by date and time. From there, I needed to make one more macro that would copy out the out of range points from the individual sheets and then paste them into one big sheet. The macro I created extracts the out of range points and neatly puts them all into one sheet so that it is easier to track trends. Finally, I recorded the macro that clears the compiled out of range data worksheet so that the master spreadsheet would be completely cleared and ready to be used next month.

With those completed, I had several macros that needed to be run in order to complete the entire procedure. I ran through the whole procedure, running each macro in the proper order to make sure they worked correctly. I did notice a few errors, nothing with the macros themselves, but an overall concern with all macros that involve copying data. When the user runs the macros, certain cells must be selected for it to run correctly. For example, most of the macros I created need to have cell A3 of the first sheet selected in order for them to copy correctly. If it is on another cell, it may copy the wrong information or it may just copy blank cells, thus making it run incorrectly. In an effort to avoid this problem, I went through each macro I created to see which ones needed that cell selected. I wrote down which macros needed it to be in cell A3 so that I could make sure to note that in the documentation. This will allow the user to know which cells need to be selected before they run the macro. With that done, it was time to make the master macro.

When starting this whole thing, I was not expecting to get complete automation. I was hoping it was possible, but I expected to run into problems at some point. However, it appears that this dream is coming true (disregarding the problem with the opening folders macro). With all the macros ready to go, I simply just recorded one more macro that ran all of the macros together in order. All the user has to do is literally open up the master spreadsheet (they may have to copy it into the new month folder first depending on what I do with the open folder macro situation) and just run the master macro. From there it will open the files, copy each device’s data into the spreadsheet, sort the data by temperature and RH, pull out the out of range points for each device, sort those out of range points by date and time and then paste them all into one sheet. From there, all the user would have to do would be to copy out that final results worksheet with all of the out of range points into another spreadsheet so that the results could be kept for each month. Once the user pulls out the results and saves them into the new month folder, they can then run the macro to clear the data in the master spreadsheet so that it can be used next month. If all goes well, the results are saved in a separate spreadsheet so that they can be viewed whenever. So months later, you could see what the data was like in February and compare it to the data in say December.

I took the results I collected for February and showed them to Pam so that she could review them. After getting the results I did notice something that needed to be changed. The final results worksheet with the out of range points was very large. It had roughly 500 out of range points for each device. There were simply too many points to make the data effective. This stemmed from the fact that we set the temperature limit to 70. There were a lot of data points in which the temperature was higher than 70. Before I could ask Pam, she noticed the same thing and she said we needed to up the limit for the temperature. The results only had one data point where RH was higher than 55%, so the RH limit was perfect and did not need to be changed. But there were way too many points above 70 degrees. So Pam decided that we should increase the limit to 73. This was a very easy fix. All I had to do was go into the out of range algorithm I made and change the greater than 70 to greater than 73. I reran the master macro, copied out the new out of range results and voila, we had far better results. There were a few out of range points for each device now, far easier to read and track trends. With this new limit, Pam should be able properly monitor the results to see where there could be some potential problems.

Pam was very excited to finally see these results. She commented to me that this was a realization of a three year dream. I was very glad that she was so excited about the data. It made me feel very good to know that I had completed what I had set out to do. I created a procedure that took the HOBO data and made it useful. This was the main objective of this project and I have completed it. So from now on, Pam can run these procedures and start collecting out of range worksheets, allowing her to track trends. In fact, if needed, she can even take some of the older data and run them through the master spreadsheet to see what some of the older data looked like. To do that however, I would have to include a section in the documentation for this as most of the old data only had four worksheets instead of six.

For the most part, the procedure is complete. I have taken the data pulled from the HOBO devices, extracted the out of range points and gotten them all into one, printable sheet. All that remains is for me to fix the folder opening problem, touch up the procedures, and document as much as possible. I need to make sure to document the new master macro procedures and I also need to document how to change the name of the devices not only in Box Car Pro, but in the master spreadsheet just in case the devices need to be renamed. I also need to document a procedure so Pam can run the master spreadsheet with the older data which only have four devices. This way she can now at least make use of all of data that has she been collecting for the last three years.

In the end this week was a very good week for the project. The project is pretty much completed. As mentioned, I do have a few things I need to touch up, but I should be completed in time to meet my deadline date of March 28, next Friday. I am glad to see that the project has gone so well. I am also glad to see that Pam is so excited about all of the procedures and the out of range results worksheets.  So far the project has gone very smoothly and most everything has gone according to plan. I am just as excited as Pam is at getting the procedures in place. With them in place, her staff will be able to help her monitor the library. I hope my efforts will help keep the mold out of the library for good.

With that said, this story is not yet at an end. Next week I will discuss the macro folder situation, showcase some of the final documentation, and I will discuss what the few remaining posts will entail. Until then, thanks for reading.

This week was another busy week for coursework. It was the week before Spring Break so I had several large projects to complete before the break. As such, I did not get as much completed on the out of range algorithm as I hoped. I did start a bit of it however, only to run into a few problems with visual basic code in the macro.

Through my work on the algorithm so far, I did realize that solution 2, which I mentioned last week, can be altered to work better. Last week I said that one of the problems with solution 2 was that it required the user to copy each sheet for each device and paste all of the data into one sheet. If the user pastes all of the data into one sheet, they would have to put a device identifier on all of the points so that you could differentiate which points belong to which device. If the user were to enter that number incorrectly, or forget to add it entirely, the data would lose its pairing with the device and then you could not tell what device gave you what data.

Well, while working on the algorithm this week, I determined that there is no need to combine all of the worksheets into one worksheet (as described in solution 2). I determined that I could create a macro that repeats the algorithm for each sheet automatically. This would allow the data to stay in separate sheets. With the data in separate sheets, you could tell what outlying points were collected for that particular device simply by looking at what worksheet you were on. By doing this, the user would not have to enter in any information. All they would need to do would be to run the macro and it would pull out the out of range points for each sheet.

I actually proposed the separate sheet idea in solution 1. However, solution 1 had problems with duplicates, which was the main reason why I went with solution 2. The funny thing is that I actually solved the duplicates problem in solution 2 by adding conditionals. The conditionals made the algorithm check both conditions at once rather than having it go back twice as it did in solution 1 (going back twice caused the duplicates). Looking back at it, solution 1 is actually closer to the right answer than solution 2. The conditionals idea from solution 2 makes solution 1 perfect, a fact that I am not entirely sure how I missed when writing solution 2. So, in the end, the best solution was combining the best parts of both solutions. Now that solution 2 has been altered, it should work perfectly. Now all I have to do is just get the macro working.

Now that I had realized the best possible solution, I had to start figuring out the algorithm. I went ahead and tried to get something created quickly, just to see if I can get it to pull out a few records before I made it run a whole sheet. I made up a quick visual basic macro that checked the conditionals I brought up last week. I had it first look to see if the temperature was great than 70. If it was, then I had it check to see if that same data point had a relative humidity greater than 55%. If it did, it copied both of those out. If the temperature was not greater than 70, I then had it check to see if the RH was above 55%. If it was, it copied out just the RH. If neither was high, it would move on to the next record.

I felt pretty good about it, so I went ahead and tried to run it. It did not run. It instead gave me an Else Without If error. This is where I am now. I am pretty sure it just has something to do with the way I setup the If Then statements. I am still not entirely familiar with the way basic does them, so I am sure one is either in the wrong place or I missed one. I wanted to look more at it this week, but due to all the projects, I was unable to. However, with Spring Break coming up next week, I should have sometime to fix the algorithm and at least get it to work on one sheet. From there I can get it to work on all of the sheets. Even though it is Spring Break, I don’t really have time for a break. I really still need to work on this algorithm. I set an end date of March 28th and that date is rapidly approaching. I will continue to work on the algorithm while on break in effort to have at least a good part of it ready to show Pam when I come back. Anyway, for now, here is what I have for the algorithm so far.

3/10 Update - DUH! As I was working with a macro at work today, it dawned on me. I had one of those ah-ha moments. I was about to run a macro that I use at least 3 times a day at work when I noticed something in the visual basic code. END IF! I completely and utterly neglected to include End If statements to close off the Ifs. As such, I kept getting the Else Without If because I was not ending the previous If. So I went ahead and corrected that and the macro works. With it working now, I went ahead and ran it. At first, I thought it was not working right. It appeared that it was just pulling out the temperatures and skipping the relative humidity. I assumed it was something I messed up in the If Thens. However, as I was running through the code, line-by-line, I realized that there is no reason for it to pull out any RHs. Before even creating the macro, I had sorted the data by temperature and then RH, both descending. When I looked at the RH column during my run through, I realized that none of the data points had a RH higher than 55% (highest was 48.5), therefore it did not pull any out. So the macro does appear to be working. Now I have to try out some more test data. This data has to include every possible option to see if the macro properly pulls out just the ones with a temperature above 70, just the points with RH above 55%, and those data points who have both high temperature and high RH. END OF 3/10 Update

I mentioned last week that in the class we were finishing up our project plans. This week, I combined all of the sections I have been working on into one rough draft document. As mentioned last week, the project plan includes sections for a work break down schedule, an executive summary, assumptions and constraints, cost estimates, and alternative and proposed solutions. Once I combined all of the pieces together, I realized that I had a very large, detailed project plan. It was a lot larger than I expected it to be. However, breaking it down as we did was a great idea. This made it seem like it was shorter and easier to manage, thus I never felt overwhelmed at any point with the project plan. Since I had all the parts already done, putting it together was the easy part. I feel pretty confident in my project plan and I feel it describes and outlines this project very well. As promised, I give you the rough draft. Click here to download the full project plan rough draft.

Well that is all I have this week. Although it was a quiet week in terms of the project, I feel I have made decent progress. I have started my work on the algorithm and I comfortable with the direction I am going in. I completed the project plan and I am confident in that. There will be no blog post next week due to Spring Break, but I assure you that I will be working on this project during my break. By my next post, I should have a solid, working algorithm. Until then, thanks for reading.

This week, I focused my efforts on the algorithm for extracting the out of range data points. However, I have yet to find a good method or finalize the steps in the process. It is proving a bit harder than I originally anticipated. I will think I have it all figured out, and then all of sudden I realize there is a problem with my idea. I am used to this however. Every time I have had to create new procedures for my job, I have found this to be the case. I will start on a method, have it all worked out in my head, only to find out later that it does not work or I have to rework it. In the end though, I usually find a solid procedure that gets the job done. So based on my prior experiences, I am confident that I will develop a good algorithm that I will soon be able to document. The need for urgency is starting to draw nearer however. My due date for all the work is March 28th and I can easily say March came far quicker than expected. But I have given myself enough time it seems. I have one month to determine an algorithm, document those procedures, and then run all of the procedures in full to make sure all of them work as intended.

With the sense of urgency kicking in, I felt I needed to wrap up some of the loose ends that I had at this point. So, I went ahead and sent Pam the documentation I developed last week so that she could review them. She was out of the office all week at a conference, so we have not had a chance to review them in full. Her preliminary responses sounded positive, so it would seem there will only need to be a few minor tweaks. After I make those tweaks, I will touch up the documentation and I will put an electronic copy on the server and I will also print a copy as well.

So this week was indeed quiet, with me mostly working on the algorithm. As such, this week, I am going to discuss a little bit about the project plan that I am writing. For the class, as assignment on top of the project, we have to a write a project plan that discusses the intricate details of our project. This week, I will discuss the alternative solutions and the proposed solution section of the project plan since it details some of my ideas and plans for the algorithm. In this section I proposed two alternative solutions for this algorithm. These solutions have been guiding my work on the algorithm. I will give details on both solutions so that you can get a feel for where I am going with my work on the algorithm. First, I must explain what will happen to the data in the six spreadsheets that are created from the documentation I developed last week. After completing the downloading procedures there will be a spreadsheet for each device, with each one having three columns, one for date and time, temperature, and relative humidity. Both solutions suggest combining the six spreadsheets into one master spreadsheet. This should make the work easier since it would put all of the data into one file. With that said, below are the details of each solution.

Solution 1

This solution begins by moving the six spreadsheets into a master spreadsheet. There will be one file with six worksheets, one for each device. You will start out by running a macro that sorts the data in each sheet by temperature. It will sort the column using descending order. This will show you the highest temperatures recorded for each device. Then the macro will go through each record to see if the temperature is above 70. If a record is above 70, it will copy out the date, time, and temperature and it will paste them to another location. Next, it will sort by relative humidity and then copy out the ones that are above 55%. Once that is completed, it will create two sections in that device’s sheet, one for temperature and one for relative humidity. Once you run the macro, everything is set up, requiring little effort from the user. From there, you now have the time and day that a particular device registered a high temperature or a high relative humidity.

Now this does come with a risk. Since it checks for temperature and relative humidity separately, there is a risk of duplicates. If the data point has a high temperature and a high relative humidity it will be pulled out twice. The risk of duplicates could be frequent to almost non-existent. It all depends on the data that is collected. There could be some data sets that have more duplicates than others and there may be some that have none at all. If there are duplicates, a way to get rid of them would be to combine the temperature and relative humidity columns and do a filter, or use Excel 2007’s new duplicate record function. So although this solution runs the risk of duplicates, it does fix the problem. It takes the data for each device and pulls out the out of range points and sets them aside. From here you can take out that data for any device and view its highest temperatures or its highest relative humidity.

Overall, this solution would get the job done, and relatively quickly. This solution is probably the most self automated. You would literally just have to put the device spreadsheets into the master spreadsheet and then just run the macro. However, in order to complete this quickly, you sacrifice some data integrity. Although this one does separate the out of range data points, duplicate records are not a good thing to have. So, I took my ideas from this solution and tried to revamp them a bit. I do this frequently when creating new procedures. As mentioned earlier, I do a lot of procedures for my job and this how it commonly goes. I will come up with an idea only to find an issue with it. Then I will build a way around that until I get to the finished product. However, I do know that if for some reason I cannot find a way around this problem with the duplicate records, I at least have a solution to fall back on that will correctly pull out the data points.

Solution 2

Like in solution one, you will combine all of the spreadsheets into one master spreadsheet. This time however, you will combine all of the data into just one sheet. So there would be one file with one sheet. When you copy the data into this one sheet, you have to mark which device the data comes from. For example, if you copy over data from device 1, you need to place L1 next to each record for that device. Then you would do the same for subsequent devices. This connects the records to its corresponding device. Then you would run another macro.

This macro will sort this one sheet by temperature and then by relative humidity. This macro will eliminate duplicates. It will first check to see if the data has a temperature above 70, if it does, it will then check to see if it has a relative humidity greater than 55%. If it has both of those, it will copy it out and put it into a new sheet, making sure that it copies out the device number. If its temperature is higher than 70 but the relative humidity is not 55%, it will still copy out to that sheet. If its temperature is not higher than 70 but its relative humidity is above 55%, it will also copy out. This will remove duplicate records. In solution one it checked to see if the temperature was above 70. If it was, it copied it out. Then later it did another check on those same records to see if its relative humidity was above 55%. If it was, it copied it out again. This led to the duplicates. This one will check both conditions at once. If it has both, it will be copied out only once. If it has one or the other, it will still be copied out, but it will never go back to that record again since it was coded to already check for the other condition. Once completed, all of the out of range data will be in one sheet, mapped by the device ID written in by the user prior to running the macro (all without duplicate records).

Solution 2 is the one I choose as my proposed solution. Like solution 1, it does get the job done. However, it will take longer to run since it has to go through many conditionals to pull out the out of range data points. This solution also depends heavily on the user placing the proper ID next to each record. If you don’t, you lose the connection between the points and the device. So there is an inherent risk with that. Although it may take more work and would take longer to run, solution 2 is far more accurate and it will give you a better picture of what is occurring. The macro will take a little while to create and will involve some low level visual basic programming, but I have worked with simple conditionals before, so I feel confident that I should get this solution to work.

That is pretty much it for the alternative and proposed solution section of the project plan that I have been writing. The project plan also includes sections for a work break down schedule, an executive summary, and assumptions and constraints. Next week, I am going to combine all of the pieces of the project plan into one document. This document will be a rough draft of the final project plan and it will be reviewed by the instructor. Next week, I will post this document so that you get a chance to see the whole project plan. For now though, you can click here to view the full alternative solutions and proposed solution section.

That wraps up this week. I covered a lot about the algorithm for extracting the out of range points. By going over the alternative solutions I have laid out my plans for the algorithm. Hopefully by next week I will have it completed and I can discuss my results in further detail. Until then, thanks for reading.