Reprinted with permission from Experts Exchange member Brent Van Scoy.
Brent Van Scoy is, in most ways, like most of the people who ask questions at Experts Exchange; he has problems that he hasn’t figured out solutions to. In other ways, however, he has situations that are not only complex but sensitive as well. In May of last year, he had one of those situations involving an Excel spreadsheet and a loop. The question itself was closed a week or so after being asked, but in August, Brent posted an update, but never got around to posting some dummy data. Then a couple of weeks ago, he posted that he’d been continuing to learn, but didn’t know how to tell everyone. We asked him to tell his story:
I wanted to take a minute to share an experience I had last year when I posted a question in the forum and thank the members who were extraordinarily generous with their time and talents. I always try my best to read and research prior to posting a question, but I was completely at a lost on how to solve my problem.
I work for a fire department, in the Midwest, which employs over 600 firefighters, 20+ Engine Houses and close to 40 apparatuses. Our department utilizes NFPA 1710 standards, which measure various fractal time responses to gauge how well we are responding to emergencies. We use Zoll’s RescueNet Fire RMS (MySql) and also Vinelight’s fire intelligence software to help manage our NFPA 1710 standards. Although both programs work very well, we wanted to take our assessment one step further and that is what leads to my question in the forum.
Like many fire departments, our city is broken up into fire zones, which are geographical markers much like zip codes. Each fire zone has one Engine, one Truck and one Medic who has the primary responsibility to respond depending on the type of emergency. The question has always come up, “If the primary unit did not respond, then where were they at the time dispatch?” There are a few reasons a unit may not respond to their primary area, but the reason we were most interested in finding out was if they were already on an incident and could not respond. The thread’s formula allows us to track this metric with great success.
After posting a less than perfect question (I am being generous with less than perfect), Dave (dlmille) responded quickly with a formula. He was patience and explained it in further detail until I understood it completely. I awarded Dave points and thought that was the end of it. It turns out it was not the end, but the beginning of an incredible learning experience for me. After the points were awarded and I thought the question was settled, Brad (byundt) joined the thread to give everyone a master lesson in Excel and also a lesson in generosity.
Without going into every detail of the thread, Brad suggested reorganizing the data (normalization) and also contacting another EE member, Zack Barresse (firefytr) who is also a Fire Captain and Excel MVP. After Zack and Brad started to work on the question it was everything I could do to try and keep up! Actually, both went out of their way to explain what each part of the formula was doing, so I could go back, read and run tests with actual data. It is at this point that they both went completely above and beyond what anyone would expect from a forum response.
It is safe to say that I did not realize the complexity of my question and each time I went back to test, I would find another problem I did not foresee. Both experts continued to help and contributed until the final solution was reached. Their solution has been used time and time again with fantastic results. I’m personally convinced it has made a big impact of the citizens of our city. Zack took it even a step further by creating a menu item that speeds up the process of formatting my data from different sources.
The solution gives the fire department the ability to track fire units with speed and accuracy that was not possible prior to the Experts’ contributions. We have other ways of tracking our units with crystal reports, GIS mapping and NFPA 1710 software but the solution from EE is my favorite. Its simplicity gives me the ability to take the results and place into pivot tables, charts and power point presentations. Over time, I have fine-tuned the process and I am able to produce results within a short amount of time. The formulas are pretty demanding on my system and I usually have to break them up into quarters so Excel doesn’t freeze up, but I have placed on the results into a table and at last glance, I had over 350,000 rows.
Until this thread, I did not realize how much EE had to offer its members. Since then I have participated in several webinars, read articles and also posted more questions in the forum. The thread inspired me to learn more about Excel and I recently completed an online course in Excel, VBA and I am starting a dashboard course next week.
I would like to thank Dave, Brad and Zack for going above and beyond to help a member of their EE community. It is greatly appreciated and has made a big difference in our little part of the world.
Brent Van Scoy
Brad’s final formula:
=IFERROR(IF(I13=1,””,E13 & ” On Incident #”&LOOKUP(2,1/((E13=$C$2:$C12)*(M13>=$M$2:$M12)*(N13<=$N$2:$N12)),$A$2:$A12)),E13 &” Out of service”) & IFERROR(IF(I13=1,””,CHAR(10) & F13 & ” On Incident #”&LOOKUP(2,1/((F13=$C$2:$C12)*(M13>=$M$2:$M12)*(N13<=$N$2:$N12)),$A$2:$A12)),CHAR(10) & F13 &” Out of service”) & IFERROR(IF(I13=1,””,CHAR(10) & G13 & ” On Incident #”&LOOKUP(2,1/((G13=$C$2:$C12)*(M13>=$M$2:$M12)*(N13<=$N$2:$N12)),$A$2:$A12)),CHAR(10) & G13 &” Out of service”)
Our final workbook, in case you think you might need it.