VBA Loop, Vlookup, Match or Index to return values
This might get a little confusing, so I hope that I do a good job of explaining myself. I have scaled this example down, but in my project there will be between 20,000 and 100,000 to evaluate. I have tried most of the day with Index, vlookup and match, but I think it might need VBA and a loop.
The basic problem is to compile response statistics demanded by NFPA 1710: STANDARD FOR THE ORGANIZATION AND DEPLOYMENT OF FIRE SUPPRESSION OPERATIONS, EMERGENCY MEDICAL OPERATIONS, AND SPECIAL OPERATIONS TO THE PUBLIC BY CAREER FIRE DEPARTMENTS which tracks several different time periods in an emergency response.
A1:E10 contains my data set for this example. Every 911 call we respond to has a fire zone, which is a geographical location on the map. We have about 175 Fire Zones and each one of those zones has (1) Engine (1) Truck and (1) Medic assigned to it. I have listed on the right hand side a sample of the zone assignments. There are 3 name ranges (Engines, Trucks and Medics). A fire zone # will only show up once in a range (There are no duplicates within the range). Example. Zone 101 will show up once in (each range) Engine, Truck and Medic, but it will never show up more than once within the same range.
Using Row 2 as an example, I would look at the value in C2, Find the correct range (engine for this one) and check to see if the value in B2 shows up in the same row (in the engine range) as the value in C2. If it does show up in the same row, then I could mark "yes", if not it would mark No.
The 2nd part would be to identify whose zone it should have been. In my example, Row 3 Fire Zone 529 was not E56's primary zone. Fire Zone 529 should have been E60 call.
I think I have this pretty mucked up by now! When I look at each row, I will only be looking at 1 unit (Engine, Medic, TR) and one zone at a time. I am attempting to identify which units are not making there primary zone alarms so I can improve response times.
I am very interested in learning VBA, but this is over my head. I do promise to study what ever help is provided so I can improve.
Thanks for any help.
Brent
EE-Times.xlsm
The basic problem is to compile response statistics demanded by NFPA 1710: STANDARD FOR THE ORGANIZATION AND DEPLOYMENT OF FIRE SUPPRESSION OPERATIONS, EMERGENCY MEDICAL OPERATIONS, AND SPECIAL OPERATIONS TO THE PUBLIC BY CAREER FIRE DEPARTMENTS which tracks several different time periods in an emergency response.
A1:E10 contains my data set for this example. Every 911 call we respond to has a fire zone, which is a geographical location on the map. We have about 175 Fire Zones and each one of those zones has (1) Engine (1) Truck and (1) Medic assigned to it. I have listed on the right hand side a sample of the zone assignments. There are 3 name ranges (Engines, Trucks and Medics). A fire zone # will only show up once in a range (There are no duplicates within the range). Example. Zone 101 will show up once in (each range) Engine, Truck and Medic, but it will never show up more than once within the same range.
Using Row 2 as an example, I would look at the value in C2, Find the correct range (engine for this one) and check to see if the value in B2 shows up in the same row (in the engine range) as the value in C2. If it does show up in the same row, then I could mark "yes", if not it would mark No.
The 2nd part would be to identify whose zone it should have been. In my example, Row 3 Fire Zone 529 was not E56's primary zone. Fire Zone 529 should have been E60 call.
I think I have this pretty mucked up by now! When I look at each row, I will only be looking at 1 unit (Engine, Medic, TR) and one zone at a time. I am attempting to identify which units are not making there primary zone alarms so I can improve response times.
I am very interested in learning VBA, but this is over my head. I do promise to study what ever help is provided so I can improve.
Thanks for any help.
Brent
EE-Times.xlsm
ASKER
That's probably not a bad suggestion, but I would rather try and learn with Excel VBA without creating a new database. Thank you for the response.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Dave, I will read over everything right now and get back.
ASKER
Dave,
I can follow what you are doing with the formulas. You are testing the beginning letters, "E", "M", but I don't see the "T", but I do see the end of the 1st. formula addressing that. I will have to read up on SUMPRODUCT a little more, but I can see that once you find the correct RangeIDX, you are matching them with the sumproduct?
The 2nd formula makes sense with the exception of the text whoshoulditbe. Not sure what it refers to? Maybe the heading?
I think the smart thing for me to do would be to use the formulas, since I can at least make sense out of them. I am very interested in the code you provided, so I will read up and study it this weekend. Even if I don't use it, I can always step through it and gather quite a bit from it.
The lines will be static. We have a web based program that will spit out my data in text format. Once, I go through it with your formula, then I will use a pivot table to come up with percentages and other important data.
I will choose this as the correct solution and I will check back to see if you have any other suggestions. Thank you very much for this lesson. As always, I will follow up in detail so I can learn.
Brent
I can follow what you are doing with the formulas. You are testing the beginning letters, "E", "M", but I don't see the "T", but I do see the end of the 1st. formula addressing that. I will have to read up on SUMPRODUCT a little more, but I can see that once you find the correct RangeIDX, you are matching them with the sumproduct?
The 2nd formula makes sense with the exception of the text whoshoulditbe. Not sure what it refers to? Maybe the heading?
I think the smart thing for me to do would be to use the formulas, since I can at least make sense out of them. I am very interested in the code you provided, so I will read up and study it this weekend. Even if I don't use it, I can always step through it and gather quite a bit from it.
The lines will be static. We have a web based program that will spit out my data in text format. Once, I go through it with your formula, then I will use a pivot table to come up with percentages and other important data.
I will choose this as the correct solution and I will check back to see if you have any other suggestions. Thank you very much for this lesson. As always, I will follow up in detail so I can learn.
Brent
ASKER
Thank you very much.
Dave,
Here is an array formula to return which engines should handle it:
=IF(COUNTIF(engines,B2)=0,
Note that there is a mix of numbers and text that looks like numbers in the Zones and lookup table engines. The formula will give the wrong answer (from row 1) whenever there is a mismatch of data type.
Note too that the use of INDEX and ROW works perfectly when engines starts in row 1. If it didn't, you'd need to subtract (the number of the first row in engines less 1):
=IF(COUNTIF(engines,B2)=0,
Brad
Here is an array formula to return which engines should handle it:
=IF(COUNTIF(engines,B2)=0,
Note that there is a mix of numbers and text that looks like numbers in the Zones and lookup table engines. The formula will give the wrong answer (from row 1) whenever there is a mismatch of data type.
Note too that the use of INDEX and ROW works perfectly when engines starts in row 1. If it didn't, you'd need to subtract (the number of the first row in engines less 1):
=IF(COUNTIF(engines,B2)=0,
Brad
ASKER
I just found whoshoulditbe. It is referring to the Option Explicit code. The 2nd formula makes sense now! thanks
>>I can follow what you are doing with the formulas. You are testing the beginning letters, "E", "M", but I don't see the "T", but I do see the end of the 1st. formula addressing that.
Well, if not Engines and Not Medics, then Trucks is the only thing left, so no need to test for that, just head down that path...
>>I will have to read up on SUMPRODUCT a little more, but I can see that once you find the correct RangeIDX, you are matching them with the sumproduct?
SUMPRODUCT can act like a find function, on a single or two dimensional array. Here's how it works:
SUMPRODUCT((engines=$B2)*(
Each of the grouped formulas inside the SUMPRODUCT will resolve to an array of TRUE or FALSE. The first resolves to a 2-D array, and the second a 1-D array which locks in the row of the 2-D array. As a result, when multiplied together, only a firezone that exists on the same row as the engine desired will be counted. In this instance, sumproduct sums up a multiplication of TRUE's and FALSES' so it ends up counting the number of occurrances that are found.
>>The 2nd formula makes sense with the exception of the text whoshoulditbe. Not sure what it refers to? Maybe the heading?
whoShouldItBe? That's the name of the user defined function.
You can go manual here, as you wish. Just select the formulas that should now be "static" and do a COPY->PASTESPECIAL->VALUE to convert them to values (but don't lose your formula!).
Dave
Well, if not Engines and Not Medics, then Trucks is the only thing left, so no need to test for that, just head down that path...
>>I will have to read up on SUMPRODUCT a little more, but I can see that once you find the correct RangeIDX, you are matching them with the sumproduct?
SUMPRODUCT can act like a find function, on a single or two dimensional array. Here's how it works:
SUMPRODUCT((engines=$B2)*(
Each of the grouped formulas inside the SUMPRODUCT will resolve to an array of TRUE or FALSE. The first resolves to a 2-D array, and the second a 1-D array which locks in the row of the 2-D array. As a result, when multiplied together, only a firezone that exists on the same row as the engine desired will be counted. In this instance, sumproduct sums up a multiplication of TRUE's and FALSES' so it ends up counting the number of occurrances that are found.
>>The 2nd formula makes sense with the exception of the text whoshoulditbe. Not sure what it refers to? Maybe the heading?
whoShouldItBe? That's the name of the user defined function.
You can go manual here, as you wish. Just select the formulas that should now be "static" and do a COPY->PASTESPECIAL->VALUE to convert them to values (but don't lose your formula!).
Dave
To handle this problem efficiently in Excel, you should normalize the data. That means there should be a two column list, with Unit repeated many times in the first column.
Unit Zone
You would then sort this list by Zone. To get the unit that should handle the incident:
=INDEX(Units,MATCH(B2,Zone
This formula will be extremely fast as long as cell B2 is always found in Zones column.
Unit Zone
You would then sort this list by Zone. To get the unit that should handle the incident:
=INDEX(Units,MATCH(B2,Zone
This formula will be extremely fast as long as cell B2 is always found in Zones column.
Brad - thanks for the tip. As with Brent, I'll be looking at THAT over the weekend, if its going to help me to understand a reverse 2-d lookup ;)
Agreed on data restructure, but I also thought that these zones might be updated/deployed in this block fashion as I recognize this from visiting the fire stations with the scouts.
Cheers,
Dave
Agreed on data restructure, but I also thought that these zones might be updated/deployed in this block fashion as I recognize this from visiting the fire stations with the scouts.
Cheers,
Dave
ASKER
Thanks for the extra comments Dave about Sumproduct. I will follow up on it.
Brad, I will take a look at what you are saying once I get a handle on it this weekend.
Thank you
Brad, I will take a look at what you are saying once I get a handle on it this weekend.
Thank you
Dave,
The only hard part to follow is the bit with MAX and ROW:
MAX((engines=B2)*ROW(engin
In an array formula, this creates a two-D array of TRUE/FALSE values multiplied by a two-D array of row numbers 1 through 15. By using MAX, we find the row number of the last match for the zone.
The INDEX then uses that row number (fortunately already one-based), and returns the Unit from the first column of named range engines.
The bit with COUNTIF is to avoid trying to return a value if there are no matches.
FWIW, I spent quite a bit of time with the Formulas...Evaluate Formula menu item to understand what was happening. It was complicated by the fact that cell B5 was text that looked like a number.
Brad
The only hard part to follow is the bit with MAX and ROW:
MAX((engines=B2)*ROW(engin
In an array formula, this creates a two-D array of TRUE/FALSE values multiplied by a two-D array of row numbers 1 through 15. By using MAX, we find the row number of the last match for the zone.
The INDEX then uses that row number (fortunately already one-based), and returns the Unit from the first column of named range engines.
The bit with COUNTIF is to avoid trying to return a value if there are no matches.
FWIW, I spent quite a bit of time with the Formulas...Evaluate Formula menu item to understand what was happening. It was complicated by the fact that cell B5 was text that looked like a number.
Brad
That's simple. I was looking at the 2-d just like that...
(engines=B2) and asking myself, now how do I get that ONE TRUE to be found. I noted the different rows, and could see the row it was on (re: F9 evaluation). So, multiplying it by ROW(engines) gives me a bunch of zeros and the ONE ROW that it would be on (assuming no duplicates). MAX giving the last match.
It could just have easily been:
SUMPRODUCT ((engines=B2)*ROW(engines)
However, I don't think subtracting and adding 1 will give the proper adjustment to create a 1-based row with each of the ranges.
Here's what I came up with, using IFERROR given Excel 2007+ to shorten the formula:
=IFERROR(IF(LEFT($C2,1)="E
See attached.
It would be interesting on the thousands of rows to know if the formula or the VBA FIND works faster... I'm liking the formula, but FIND is very fast.
Thanks, Brad - that was an education!
Dave
EE-Times-r2.xlsm
(engines=B2) and asking myself, now how do I get that ONE TRUE to be found. I noted the different rows, and could see the row it was on (re: F9 evaluation). So, multiplying it by ROW(engines) gives me a bunch of zeros and the ONE ROW that it would be on (assuming no duplicates). MAX giving the last match.
It could just have easily been:
SUMPRODUCT ((engines=B2)*ROW(engines)
However, I don't think subtracting and adding 1 will give the proper adjustment to create a 1-based row with each of the ranges.
Here's what I came up with, using IFERROR given Excel 2007+ to shorten the formula:
=IFERROR(IF(LEFT($C2,1)="E
See attached.
It would be interesting on the thousands of rows to know if the formula or the VBA FIND works faster... I'm liking the formula, but FIND is very fast.
Thanks, Brad - that was an education!
Dave
EE-Times-r2.xlsm
Dave,
In essence, you are subtracting the number of the row before the lookup table starts. In my original formula, that is 0--which is why the formula worked. For the trucks range, it would be 16 (trucks starts on row 17).
=IF(COUNTIF(engines,B2)=0,
=IF(COUNTIF(trucks,B2)=0,"
=IF(COUNTIF(medics,B2)=0,"
Brad
In essence, you are subtracting the number of the row before the lookup table starts. In my original formula, that is 0--which is why the formula worked. For the trucks range, it would be 16 (trucks starts on row 17).
=IF(COUNTIF(engines,B2)=0,
=IF(COUNTIF(trucks,B2)=0,"
=IF(COUNTIF(medics,B2)=0,"
Brad
I get that. My approach calculated the position rather than hardcoding.
We're in sync.
Dave
We're in sync.
Dave
If I didn't use the named ranges for the INDEX, then I don't need to hard-code the value to subtract:
=IF(COUNTIF(engines,B2)=0,
=IF(COUNTIF(trucks,B2)=0,"
=IF(COUNTIF(medics,B2)=0,"
=IF(COUNTIF(engines,B2)=0,
=IF(COUNTIF(trucks,B2)=0,"
=IF(COUNTIF(medics,B2)=0,"
ASKER
I will set it all up this morning and report back on how it goes.
thanks
thanks
Brent,
If the list is complete, you can return the desired type and unit of first responder with this array-entered formula:
=INDEX(G:G,CHOOSE(MATCH(LE
If the list may not be complete, then you need to use:
=CHOOSE(MATCH(LEFT(C2,1),{
Array-entered means that you hold Control and Shift keys down when hitting Enter. Excel responds by adding curly braces { } surrounding the formula.
Brad
EE-Times.xlsm
If the list is complete, you can return the desired type and unit of first responder with this array-entered formula:
=INDEX(G:G,CHOOSE(MATCH(LE
If the list may not be complete, then you need to use:
=CHOOSE(MATCH(LEFT(C2,1),{
Array-entered means that you hold Control and Shift keys down when hitting Enter. Excel responds by adding curly braces { } surrounding the formula.
Brad
EE-Times.xlsm
ASKER
Brad,
I am not sure what you mean by is the list complete?
I do understand how to make it an array.
thank you
I am not sure what you mean by is the list complete?
I do understand how to make it an array.
thank you
By list is complete, I meant that each of the named ranges engines, trucks and medics includes all the possible zones. If so, you don't need to test for a zone possibly being not present (in engines, trucks or medics) when you are searching for the corresponding unit.
Most likely, your three named ranges will be complete lists. If so, the shorter formula will compute faster.
Brad
Most likely, your three named ranges will be complete lists. If so, the shorter formula will compute faster.
Brad
ASKER
yes, the list will be complete. As part of my importing the list, I filter out any non-valid entries. We only get a few, but they are easy to spot ahead of time, so I keep a clean list in excel.
I will run both formulas and then get back over the weekend with my results. I have read up on all the formulas both of you have used, now I just need to step through it.
Thanks to both of you for taking the extra time with my questions. As always, it is greatly appreciated.
Brent
I will run both formulas and then get back over the weekend with my results. I have read up on all the formulas both of you have used, now I just need to step through it.
Thanks to both of you for taking the extra time with my questions. As always, it is greatly appreciated.
Brent
Brent,
As I had previously mentioned, the better way to solve the problem is to normalize the data. Here is a macro that will do so, creating a worksheet called Normalized along with named ranges engines2, trucks2 and medics2.
You may use these new named ranges to solve your problem very quickly using the "sorted" form of VLOOKUP:
=VLOOKUP(B2,CHOOSE(MATCH(L
=IF(VLOOKUP(B2,CHOOSE(MATC
If all the zones line up, you could further simplify the problem to use a single lookup table, with zones in column 1 and units in columns 2, 3 or 4.
=VLOOKUP(B2,LookupTable,MA
I'll leave the demonstration of this latter formula to you.
Brad
EE-Times.xlsm
As I had previously mentioned, the better way to solve the problem is to normalize the data. Here is a macro that will do so, creating a worksheet called Normalized along with named ranges engines2, trucks2 and medics2.
Sub Normalizer()
Dim rg As Range
Dim v As Variant, vData As Variant, vResults As Variant
Dim nm As Name
Dim wsNormalized As Worksheet
Dim i As Long, ii As Long, j As Long, k As Long, n As Long, nCols As Long, nRows As Long
On Error Resume Next
Set wsNormalized = Worksheets("Normalized")
If wsNormalized Is Nothing Then
Set wsNormalized = Worksheets.Add(after:=Worksheets(Worksheets.Count))
wsNormalized.Name = "Normalized"
End If
On Error GoTo 0
With wsNormalized
For Each v In Array("engines", "trucks", "medics")
vData = Range(v).Value
nRows = UBound(vData)
nCols = UBound(vData, 2)
n = Application.CountA(vData) - nRows
ReDim vResults(1 To n, 1 To 2)
ii = 0
For i = 1 To nRows
For j = 2 To nCols
If vData(i, j) = "" Then Exit For
ii = ii + 1
vResults(ii, 1) = CLng(vData(i, j)) 'Zone
vResults(ii, 2) = vData(i, 1) 'Unit
Next
Next
Set rg = .Cells(1, k + 1).Resize(n, 2)
rg.Value = vResults
With .Sort
.SortFields.Clear
.SortFields.Add Key:=rg.Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending
.SetRange rg
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
On Error Resume Next
Set nm = ActiveWorkbook.Names(v & "2")
On Error GoTo 0
If nm Is Nothing Then
Set nm = ActiveWorkbook.Names.Add(Name:=v & "2", RefersTo:="=1")
End If
nm.RefersTo = "=" & .Name & "!" & rg.Address
Set nm = Nothing
k = k + 2
Next
End With
End SubYou may use these new named ranges to solve your problem very quickly using the "sorted" form of VLOOKUP:
=VLOOKUP(B2,CHOOSE(MATCH(L
=IF(VLOOKUP(B2,CHOOSE(MATC
If all the zones line up, you could further simplify the problem to use a single lookup table, with zones in column 1 and units in columns 2, 3 or 4.
=VLOOKUP(B2,LookupTable,MA
I'll leave the demonstration of this latter formula to you.
Brad
EE-Times.xlsm
ASKER
Brad,
I will print all of this out and go over it this weekend with Dave's formula's also. I will post back on Monday to share what I did.
I was able to run the formulas from both of you and they worked perfect. I only used 1,500 rows as a sample, but I am preping everything else for Monday to run the normal numbers. Usually about 60,000 rows per year. At times, I look at 3 years combined.
Thank you for the code and I'll be back in touch.
Brent
I will print all of this out and go over it this weekend with Dave's formula's also. I will post back on Monday to share what I did.
I was able to run the formulas from both of you and they worked perfect. I only used 1,500 rows as a sample, but I am preping everything else for Monday to run the normal numbers. Usually about 60,000 rows per year. At times, I look at 3 years combined.
Thank you for the code and I'll be back in touch.
Brent
ASKER
This song keeps going through my head as everyone in the house is asleep and I am studying the thread. What an exciting Saturday night!
Another Saturday night and I ain't got nobody
I've got some money cause I just got paid
How I wish I had someone to talk to
I'm in an awful way
Dave is correct, the list is formatted in this layout at each station. They are called Zone Books and when we have a multiple alarm fires, we look up where we need to be. All rigs that are going to the fire are dispatched, but everyone else looks to find out if they are being "moved up" to another fire station. This fills in the holes created by several rigs being at the fire.
It is not a big deal to normalize the list and it appears that it makes the formula easier to understand. Back to work...
Another Saturday night and I ain't got nobody
I've got some money cause I just got paid
How I wish I had someone to talk to
I'm in an awful way
Dave is correct, the list is formatted in this layout at each station. They are called Zone Books and when we have a multiple alarm fires, we look up where we need to be. All rigs that are going to the fire are dispatched, but everyone else looks to find out if they are being "moved up" to another fire station. This fills in the holes created by several rigs being at the fire.
It is not a big deal to normalize the list and it appears that it makes the formula easier to understand. Back to work...
I wish that Zack Barresse had seen this thread early on. His screen name is fyrfyter, reflecting his day job as Captain of the Fire Department in his home town. He has also been an Excel MVP for the past seven years, so he may have a well-tested solution to this problem.
http://www.linkedin.com/pub/zack-barresse/1/3/569
http://www.linkedin.com/pub/zack-barresse/1/3/569
ASKER
I will look up him, just to say hello. thanks for the tip. Still working on the thread. As usual, I am learning a lot!
Thanks
Thanks
ASKER
I have worked my way down up until Sub Normalizer() Macro. I am understanding most of it, but now I am focusing on your suggestion of normalizing the list. It takes a complex (to me) formula and brings it into focus. I am stepping through your code right now.
The worksheet is exported out of the MySQL database in TEXT format. After reading your post about the text being confusing, "Would I be better of to convert the zones from text to numbers? Or since I will be using the looker, it would not matter?"
Along with the zones and engines, I also collect a bunch of different times for each incident (fire call). I convert those into mm:ss and then pivot for averages, standard deviations, histograms, ect...
Thank you
The worksheet is exported out of the MySQL database in TEXT format. After reading your post about the text being confusing, "Would I be better of to convert the zones from text to numbers? Or since I will be using the looker, it would not matter?"
Along with the zones and engines, I also collect a bunch of different times for each incident (fire call). I convert those into mm:ss and then pivot for averages, standard deviations, histograms, ect...
Thank you
VLOOKUP and MATCH both require that the look-up value and look-up column both be text or both be numbers. "5" is not the same as 5. If you are looking for "5" and the look-up column contains 5, VLOOKUP and MATCH won't find it.
My Normalizer sub converts the zones into numbers if they aren't already.
The easy way to convert text that looks like numbers into real numbers (so that you can use them in the VLOOKUP) is:
1) Copy a blank cell
2) Select the column (or range) containing the text to be converted
3) Paste Special...Add
Blanks, cells that already contain numbers and cells that contain non-numeric text (e.g. "Brent van Scoy") won't be affected.
Dates and times are stored by Excel as numbers. So you may be able to use the above trick to convert them as well, though you will need to format the result to look like a date or time after the conversion. Each day is a whole number. Times are fractional parts of a day, so 6 PM on May 6, 2012 would be stored as 41035.75
My Normalizer sub converts the zones into numbers if they aren't already.
The easy way to convert text that looks like numbers into real numbers (so that you can use them in the VLOOKUP) is:
1) Copy a blank cell
2) Select the column (or range) containing the text to be converted
3) Paste Special...Add
Blanks, cells that already contain numbers and cells that contain non-numeric text (e.g. "Brent van Scoy") won't be affected.
Dates and times are stored by Excel as numbers. So you may be able to use the above trick to convert them as well, though you will need to format the result to look like a date or time after the conversion. Each day is a whole number. Times are fractional parts of a day, so 6 PM on May 6, 2012 would be stored as 41035.75
ASKER
Okay, I am familiar with the formats and how to convert. So, I will make sure that the zones are numbers in my set of data I am evaluating.
I'll continue on with the macro and last formulas later this evening.
Thanks
I'll continue on with the macro and last formulas later this evening.
Thanks
ASKER
I ran the code and I only see 1 new name range (engine2) which is set to the medics column E:F row 192. I can easily just create the 3 name ranges, which I did.
You may use these new named ranges to solve your problem very quickly using the "sorted" form of VLOOKUP:
=VLOOKUP(B2,CHOOSE(MATCH(L
I thought I would use just 1 lookup table, but I can't. We have 178 Zones and the zones are broken up for Engines, Trucks and Medics. So there would be a Engine that is assigned Z101, a truck assigned to Z101 and a medic for Z101. I think that is why I set up like I did, so each apparatus would have a unique number for it's group. I totally get and understand
the formula in the E column.
I went back to look at the D column (first alarm response?) and there were no formulas. I went back to Brad's last formula, copy/pasted and I got " #name?". So I will need to add the name ranges EngineIDX, TrucksIDX and MedicsIDX, which I can do.
But, I thought why can't I just use a simple if statement in the D column? (with the example I uploaded with this post).
=IF(C3=E3,"Yes","No")
Would that be OK to do? or am I missing something?
I should point out that our Fire Zones are subject to change, but they do not change very often. We changed them last year and it was 7 years since the last change. It's a huge deal to change them because 911 has to make all the changes in their database, which is expensive and complex.
Thanks
range-question.xlsm
You may use these new named ranges to solve your problem very quickly using the "sorted" form of VLOOKUP:
=VLOOKUP(B2,CHOOSE(MATCH(L
I thought I would use just 1 lookup table, but I can't. We have 178 Zones and the zones are broken up for Engines, Trucks and Medics. So there would be a Engine that is assigned Z101, a truck assigned to Z101 and a medic for Z101. I think that is why I set up like I did, so each apparatus would have a unique number for it's group. I totally get and understand
the formula in the E column.
I went back to look at the D column (first alarm response?) and there were no formulas. I went back to Brad's last formula, copy/pasted and I got " #name?". So I will need to add the name ranges EngineIDX, TrucksIDX and MedicsIDX, which I can do.
But, I thought why can't I just use a simple if statement in the D column? (with the example I uploaded with this post).
=IF(C3=E3,"Yes","No")
Would that be OK to do? or am I missing something?
I should point out that our Fire Zones are subject to change, but they do not change very often. We changed them last year and it was 7 years since the last change. It's a huge deal to change them because 911 has to make all the changes in their database, which is expensive and complex.
Thanks
range-question.xlsm
Brent,
You are quite correct on the simple formula for Yes and No:
=IF(C3=E3,"Yes","No") This is a perfect way to solve this problem
In continuation of our preceding discussion on numbers versus text that looks like numbers, cell B11 is text. That's why your formula in E11 wasn't working. So I used the File...Options...Formulas menu item to check the box for "Numbers formatted as text or preceded by an apostrophe". As a result, you get green triangles where you might have an issue.
It is possible to force the conversion of text into a number by multiplying it by -1 twice in a row. This is done by the unary operator (looks like two minus signs in succession --). I made this change in columns E and F formulas:
=VLOOKUP(--B2,CHOOSE(MATCH
=IFERROR(VLOOKUP(--B2,look
I also saw that I had led you astray regarding how to build the named range lookuptable. It is supposed to be four columns, with one row for each zone. The zones are listed in the first column, and the responsible units are listed in columns 2, 3 and 4.
Next, I had to populate the cells in lookuptable. Because you are using a reduced subset of the actual data, some zones don't have a responsible engine, truck or medic. I put an empty string (looks like a blank) in the table for those situations.
I was expecting every zone to have one (and only one) responsible engine, truck or medic. If so, you can make lookuptable by inserting two blank columns to the right of engines2 and paste the second column of trucks2 and medics2.
Note that zone 210 is assigned to both M21 and M24--which is a problem for the Yes/No formula in D2 and other formulas suggested in this thread--they will only return one of the two values. Please advise if this is a typo--or a new requirement.
Due to the missing data, I decided to use formulas to build lookuptable. I manually populated cells H1:H3, then used a formula to determine the rest of the zones.
The responsible units were returned by copying down these fairly simple formulas in columns I, J and K:
=IFERROR(VLOOKUP($H1,engin
=IFERROR(VLOOKUP($H1,truck
=IFERROR(VLOOKUP($H1,medic
The rest of the zones could be returned by (in cell H4 and copied down):
=MIN(IFERROR(INDEX(engines
Breaking this formula apart, it is looking for the next zone in each of the three tables: engines2, trucks2 and medics2. It then uses the MIN function to pick the smallest.
INDEX(engines2,,1) returns all of the first column of engines2
MATCH(H3,INDEX(engines2,,1
MATCH(H3,INDEX(engines2,,1
INDEX(engines2,MATCH(H3,IN
IFERROR(INDEX(engines2,MAT
=MIN(IFERROR(INDEX(engines
Brad
range-question.xlsm
You are quite correct on the simple formula for Yes and No:
=IF(C3=E3,"Yes","No") This is a perfect way to solve this problem
In continuation of our preceding discussion on numbers versus text that looks like numbers, cell B11 is text. That's why your formula in E11 wasn't working. So I used the File...Options...Formulas menu item to check the box for "Numbers formatted as text or preceded by an apostrophe". As a result, you get green triangles where you might have an issue.
It is possible to force the conversion of text into a number by multiplying it by -1 twice in a row. This is done by the unary operator (looks like two minus signs in succession --). I made this change in columns E and F formulas:
=VLOOKUP(--B2,CHOOSE(MATCH
=IFERROR(VLOOKUP(--B2,look
I also saw that I had led you astray regarding how to build the named range lookuptable. It is supposed to be four columns, with one row for each zone. The zones are listed in the first column, and the responsible units are listed in columns 2, 3 and 4.
Next, I had to populate the cells in lookuptable. Because you are using a reduced subset of the actual data, some zones don't have a responsible engine, truck or medic. I put an empty string (looks like a blank) in the table for those situations.
I was expecting every zone to have one (and only one) responsible engine, truck or medic. If so, you can make lookuptable by inserting two blank columns to the right of engines2 and paste the second column of trucks2 and medics2.
Note that zone 210 is assigned to both M21 and M24--which is a problem for the Yes/No formula in D2 and other formulas suggested in this thread--they will only return one of the two values. Please advise if this is a typo--or a new requirement.
Due to the missing data, I decided to use formulas to build lookuptable. I manually populated cells H1:H3, then used a formula to determine the rest of the zones.
The responsible units were returned by copying down these fairly simple formulas in columns I, J and K:
=IFERROR(VLOOKUP($H1,engin
=IFERROR(VLOOKUP($H1,truck
=IFERROR(VLOOKUP($H1,medic
The rest of the zones could be returned by (in cell H4 and copied down):
=MIN(IFERROR(INDEX(engines
Breaking this formula apart, it is looking for the next zone in each of the three tables: engines2, trucks2 and medics2. It then uses the MIN function to pick the smallest.
INDEX(engines2,,1) returns all of the first column of engines2
MATCH(H3,INDEX(engines2,,1
MATCH(H3,INDEX(engines2,,1
INDEX(engines2,MATCH(H3,IN
IFERROR(INDEX(engines2,MAT
=MIN(IFERROR(INDEX(engines
Brad
range-question.xlsm
Brent,
I figured out why trucks2 and medics2 weren't being added as named ranges, and why engines2 referred to the wrong cells. It was because I wasn't resetting variable nm to Nothing. Adding that one statement (near the very end of the sub) fixes the problem.
Brad
I figured out why trucks2 and medics2 weren't being added as named ranges, and why engines2 referred to the wrong cells. It was because I wasn't resetting variable nm to Nothing. Adding that one statement (near the very end of the sub) fixes the problem.
Sub Normalizer()
Dim rg As Range
Dim v As Variant, vData As Variant, vResults As Variant
Dim nm As Name
Dim wsNormalized As Worksheet
Dim i As Long, ii As Long, j As Long, k As Long, n As Long, nCols As Long, nRows As Long
On Error Resume Next
Set wsNormalized = Worksheets("Normalized")
If wsNormalized Is Nothing Then
Set wsNormalized = Worksheets.Add(after:=Worksheets(Worksheets.Count))
wsNormalized.Name = "Normalized"
End If
On Error GoTo 0
With wsNormalized
For Each v In Array("engines", "trucks", "medics")
vData = Range(v).Value
nRows = UBound(vData)
nCols = UBound(vData, 2)
n = Application.CountA(vData) - nRows
ReDim vResults(1 To n, 1 To 2)
ii = 0
For i = 1 To nRows
For j = 2 To nCols
If vData(i, j) = "" Then Exit For
ii = ii + 1
vResults(ii, 1) = CLng(vData(i, j)) 'Zone
vResults(ii, 2) = vData(i, 1) 'Unit
Next
Next
Set rg = .Cells(1, k + 1).Resize(n, 2)
rg.Value = vResults
With .Sort
.SortFields.Clear
.SortFields.Add Key:=rg.Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending
.SetRange rg
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
On Error Resume Next
Set nm = ActiveWorkbook.Names(v & "2")
On Error GoTo 0
If nm Is Nothing Then
Set nm = ActiveWorkbook.Names.Add(Name:=CStr(v & "2"), RefersTo:="=1")
End If
nm.RefersTo = "=" & .Name & "!" & rg.Address
Set nm = Nothing
k = k + 2
Next
End With
End SubBrad
ASKER
Funny, I was looking at the big picture and missed a simple format issue!
I will work on this in the morning. I also want to double check my zones, because I too would think that every zone would have an Engine, Truck and Medic assigned to it. The list was populated by my boss from our zone book, but our main objective is to track when an apparatus is not the first responding rig to a zone. This identifies heavy call volume areas based on out of service rigs, which allows us to better serve a zone.
Thanks
brent
BTW, I just seen the additional code and will look at it.
I will work on this in the morning. I also want to double check my zones, because I too would think that every zone would have an Engine, Truck and Medic assigned to it. The list was populated by my boss from our zone book, but our main objective is to track when an apparatus is not the first responding rig to a zone. This identifies heavy call volume areas based on out of service rigs, which allows us to better serve a zone.
Thanks
brent
BTW, I just seen the additional code and will look at it.
ASKER
I looked all this over and I get it. I added all the tucks and zones. I attempted to run the normalized(), but keep getting an error on this line
With wsNormalized
For Each v In Array("engines", "trucks", "medics")
vData = Range(v).Value
nRows = UBound(vData)
nCols = UBound(vData, 2)
n = Application.CountA(vData) - nRows
ReDim vResults(1 To n, 1 To 2)
ii = 0
For i = 1 To nRows
For j = 2 To nCols
If vData(i, j) = "" Then Exit For
ii = ii + 1
vResults(ii, 1) = CLng(vData(i, j)) 'Zone
vResults(ii, 2) = vData(i, 1) 'Unit
I must have changed a name or something will inserting the new data. I'll continue to look for it. Thanks
All-Fire-Zones-Included.xlsm
With wsNormalized
For Each v In Array("engines", "trucks", "medics")
vData = Range(v).Value
nRows = UBound(vData)
nCols = UBound(vData, 2)
n = Application.CountA(vData) - nRows
ReDim vResults(1 To n, 1 To 2)
ii = 0
For i = 1 To nRows
For j = 2 To nCols
If vData(i, j) = "" Then Exit For
ii = ii + 1
vResults(ii, 1) = CLng(vData(i, j)) 'Zone
vResults(ii, 2) = vData(i, 1) 'Unit
I must have changed a name or something will inserting the new data. I'll continue to look for it. Thanks
All-Fire-Zones-Included.xlsm
My guess is that one of the zones has a value like "Z103". This value cannot be converted into a Long integer, and will result in a fatal error. Should this be the case, then change the statement to:
vResults(ii, 1) = vData(i, j) 'Zone
When you get dumped into the debugger, you can hold the cursor over a variable and see what its value is. In this case, it would be desirable to know what are the values of v, i, j, and vData(i,j).
Another way to get a value is using the Immediate Pane. If it isn't already displayed beneath the code pane, you can display it with View...Immediate Window in the VBA Editor. Once displayed, you can use the Immediate pane to display a value by preceding it with a question mark:
?vData(i,j) 'When you hit Enter after typing this statement, the next line is the value
Brad
vResults(ii, 1) = vData(i, j) 'Zone
When you get dumped into the debugger, you can hold the cursor over a variable and see what its value is. In this case, it would be desirable to know what are the values of v, i, j, and vData(i,j).
Another way to get a value is using the Immediate Pane. If it isn't already displayed beneath the code pane, you can display it with View...Immediate Window in the VBA Editor. Once displayed, you can use the Immediate pane to display a value by preceding it with a question mark:
?vData(i,j) 'When you hit Enter after typing this statement, the next line is the value
Brad
ASKER
Okay. I'll find it. thanks
Note: yes, that fixed it. I'll follow your clues to find it also. Thanks.
Note: yes, that fixed it. I'll follow your clues to find it also. Thanks.
ASKER
Brad and Dave,
I spent most of the day cleaning up the zones and running a fairly small sample of about $5,000 runs. It went perfect. I just need to spend a little time of charting and it will be a 100% successful project. As always, I learned a lot of new stuff! Thank you for taking the time and walking me through it. I took a 10 hour DVD course on VBA for excel, but I am going to have to find something that will teach me more depth. Maybe a semester at our local college might help.
Thanks
Brent
I spent most of the day cleaning up the zones and running a fairly small sample of about $5,000 runs. It went perfect. I just need to spend a little time of charting and it will be a 100% successful project. As always, I learned a lot of new stuff! Thank you for taking the time and walking me through it. I took a 10 hour DVD course on VBA for excel, but I am going to have to find something that will teach me more depth. Maybe a semester at our local college might help.
Thanks
Brent
Brent,
From past experience coaching other people on VBA, once you get an introduction to the basics (and your 10 hour DVD course certainly qualifies), you learn a lot faster by trying to solve real problems of importance to your job or outside pursuits. This is because you will have the motivation to keep digging until the code works.
There are almost always more than one way of solving a problem. The depth you refer to comes from digging into method A until you either get it to work, or move on to methods B, C and D.
Almost everybody starts by recording macros, and then trying to generalize them. Assuming you eliminate the inefficiencies of using code exactly as the macro recorder wrote it down, the differences in computation time and coding time between methods A, B, C, and D aren't too important. But if you do get stuck, Experts Exchange is here to provide a helping hand.
When you do get done with this particular project, could you post a description of exactly what you did? This would include the final code, formulas, and data conversion--perhaps applied to the sample workbook you have been using. The solution to your problem is relevant to police and fire departments in large cities around the world, and I'd like to make it available to them in the Experts Exchange database.
Brad
From past experience coaching other people on VBA, once you get an introduction to the basics (and your 10 hour DVD course certainly qualifies), you learn a lot faster by trying to solve real problems of importance to your job or outside pursuits. This is because you will have the motivation to keep digging until the code works.
There are almost always more than one way of solving a problem. The depth you refer to comes from digging into method A until you either get it to work, or move on to methods B, C and D.
Almost everybody starts by recording macros, and then trying to generalize them. Assuming you eliminate the inefficiencies of using code exactly as the macro recorder wrote it down, the differences in computation time and coding time between methods A, B, C, and D aren't too important. But if you do get stuck, Experts Exchange is here to provide a helping hand.
When you do get done with this particular project, could you post a description of exactly what you did? This would include the final code, formulas, and data conversion--perhaps applied to the sample workbook you have been using. The solution to your problem is relevant to police and fire departments in large cities around the world, and I'd like to make it available to them in the Experts Exchange database.
Brad
ASKER
Brad,
That is exactly what I have been doing. Recording the macro, making changes, then putting it together. I refer back to my course with notes, DVD's and files. I make an honest effort to solve the problem and if not, I take what I learn from EE, study, research and refer back to all the time to my previous post.
I would be happy to post a description with your code, formulas and a sample workbook. I will need to substitute my actual data, but it will be easy to do. NFPA 1710 (national guidelines for response times) is emerging as a way to help manage department's budgets and manpower.
I will post back in a few days.
Thanks,
Brent
That is exactly what I have been doing. Recording the macro, making changes, then putting it together. I refer back to my course with notes, DVD's and files. I make an honest effort to solve the problem and if not, I take what I learn from EE, study, research and refer back to all the time to my previous post.
I would be happy to post a description with your code, formulas and a sample workbook. I will need to substitute my actual data, but it will be easy to do. NFPA 1710 (national guidelines for response times) is emerging as a way to help manage department's budgets and manpower.
I will post back in a few days.
Thanks,
Brent
Brent,
I see that Section 6.2 of NFPA 1710 requires you to maintain the records (and do the analysis) that you are doing. And while each Fire Department in the country is required to comply, the details appear to be left up to the Fire Department.
Is it really the Wild West out there--or are there software packages available that help smaller Fire Districts comply with the regulations?
Brad
I see that Section 6.2 of NFPA 1710 requires you to maintain the records (and do the analysis) that you are doing. And while each Fire Department in the country is required to comply, the details appear to be left up to the Fire Department.
Is it really the Wild West out there--or are there software packages available that help smaller Fire Districts comply with the regulations?
Brad
ASKER
Brad,
I am not an expert in NFPA 1710, but I do believe they are recommendations, not mandates.
Yes, there are software packages available. We use a company called Vinelight. Most of them seems to be pretty fancy excel dashboards which tie into the department's databases. Vinelight does a nice job of bringing the data together, but it is still lacking in reports. My boss is the one who deals with all the vendors, so I am not very familiar with them besides a little interface with their program. It's a limited, but easy to use. I like to export out of their program and into Excel. We are a pretty big department, so I am not sure what the smaller ones do.
BTW, I ran the code a little bit ago on 30,000 rows and it was very fast. I am still working around the edges of a few problems, but it's with the zones, not so much the formulas (rigs go in and out of service, ect). Also, I got a hold of Zach and he seems to be a very nice guy.
Thanks
Bren
I am not an expert in NFPA 1710, but I do believe they are recommendations, not mandates.
Yes, there are software packages available. We use a company called Vinelight. Most of them seems to be pretty fancy excel dashboards which tie into the department's databases. Vinelight does a nice job of bringing the data together, but it is still lacking in reports. My boss is the one who deals with all the vendors, so I am not very familiar with them besides a little interface with their program. It's a limited, but easy to use. I like to export out of their program and into Excel. We are a pretty big department, so I am not sure what the smaller ones do.
BTW, I ran the code a little bit ago on 30,000 rows and it was very fast. I am still working around the edges of a few problems, but it's with the zones, not so much the formulas (rigs go in and out of service, ect). Also, I got a hold of Zach and he seems to be a very nice guy.
Thanks
Bren
Hello all,
@Brad: Thanks for pointing out the introduction! FWIW, NFPA is not required by all State's, including mine (Oregon). It's kind of a double-edged sword. There's nothing in our statues or rules which tell us we must comply with NFPA, although we do have our own State laws. We (my department) try to adhere to the NFPA standards as best we can, although as small as we are it's almost impossible. The other edge to the sword is when it comes court time, the lawyers will be all over the NFPA standards, and as the premiere de facto standard, we will most likely be held to it. Catch 22.
I'm not sure what the speed was, but I started trying to normalize the data before I saw the Normalizer() subroutine. It was pretty obvious it's what was needed to work with the data in a normalized manner. Here's my spin. I don't know what the speed comparison would be as it's not a true comparison, it doesn't set any named ranges.
You could, if you wanted, return all units in a single cell for that zone (primary response). here are a couple of UDF's designed to do so. One is designed for use with VBA (i.e. with another routine and not as a worksheet function), deftly suffixed with "VBA", and the other is designed for use as a worksheet function, deftly suffixed with "WF".
VBA version:
Worksheet Function version:
You would call the WF version in a cell with the following formula (as an example)...
Or you could reference the entire column as such...
If you notice, in both functions you have the ability to refrain from identifying specific unit types (i.e. Engines), by passing a True or False (boolean) value to the appropriate arguments. Since there's no worksheet function intellisense with this type of UDF you could pass all arguments for maintainability's sake...
This would go in the "1st Alarm Response?" column. If you used that, you could use something like this for the "Who's should it be?" column, albeit a bit long winded...
And a simple boolean match...
Or to boil down to a 1 or 0 ...
Personally I prefer that way for easier data manipulation, plus conditional formatting is easier that way.
I do realize this is 1) a lot, and 2) not *quite* what you asked for, but hopefully it gives you options. I've made the changes to the file and have uploaded with these changes in place (on another worksheet). I've also added a PivotTable, not sure if it'll help or not. I do wonder about performance for 100k+ rows of data though. I doubt it would be better than the straight-up formula approach with the named ranges.
Another option for named ranges would be something like this (dynamic and not set via code, and assuming you have that third column like my code creates in the NormalizedZB worksheet, column C - Type)...
Engines:
Medics:
Trucks:
One big downside with using functions like this (really it's just using the 1 as the third syntax in MATCH() function) is your data must be sorted (on the NormalizedZB worksheet) by column C. It shouldn't affect the other code, but that ISN'T built into any of the code either.
Okay, I think I'm done. :/
HTH
Regards,
Zack Barresse
All-Fire-Zones-Included-ZB.xlsm
@Brad: Thanks for pointing out the introduction! FWIW, NFPA is not required by all State's, including mine (Oregon). It's kind of a double-edged sword. There's nothing in our statues or rules which tell us we must comply with NFPA, although we do have our own State laws. We (my department) try to adhere to the NFPA standards as best we can, although as small as we are it's almost impossible. The other edge to the sword is when it comes court time, the lawyers will be all over the NFPA standards, and as the premiere de facto standard, we will most likely be held to it. Catch 22.
I'm not sure what the speed was, but I started trying to normalize the data before I saw the Normalizer() subroutine. It was pretty obvious it's what was needed to work with the data in a normalized manner. Here's my spin. I don't know what the speed comparison would be as it's not a true comparison, it doesn't set any named ranges.
Option Explicit
Sub NormalizerZB()
Dim wsNormalized As Worksheet
Dim WS As Worksheet
Dim iLastRow As Long
Dim iRow As Long
Dim iCol As Long
Dim iLoop As Long
Dim iInnerLoop As Long
Dim iInnerStart As Long
Dim iInnerEnd As Long
Dim iColIndex As Long
Dim iRowIndex As Long
Dim xVal As Variant
Const sColumnStart As String = "G"
Const wsNormalizedName As String = "NormalizedZB"
Set WS = ThisWorkbook.Worksheets(1)
On Error Resume Next
Set wsNormalized = ThisWorkbook.Worksheets(wsNormalizedName)
If wsNormalized Is Nothing Then
Set wsNormalized = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(Worksheets.Count))
wsNormalized.Name = wsNormalizedName
Else
wsNormalized.Cells.Clear
End If
On Error GoTo 0
wsNormalized.Range("A1").Value = "Zone"
wsNormalized.Range("B1").Value = "Primary Unit"
wsNormalized.Range("C1").Value = "Type"
iLastRow = WS.Cells(WS.Rows.Count, "G").End(xlUp).Row
iRowIndex = 2
iColIndex = StartCol(sColumnStart)
If iColIndex = 0 Then
MsgBox "There was an error establishing the proper starting column.", vbExclamation, "ERROR!"
Exit Sub
End If
Call TOGGLEEVENTS(False)
For iRow = 1 To iLastRow
If WS.Cells(iRow, iColIndex).Value = vbNullString Then GoTo SkipRow
For iCol = iColIndex + 1 To WS.Cells(iRow, WS.Columns.Count).End(xlToLeft).Column
If InStr(1, WS.Cells(iRow, iCol).Value, "-", vbTextCompare) > 0 Then
xVal = WS.Cells(iRow, iCol).Value
iInnerStart = CLng(Left(xVal, InStr(1, xVal, "-", vbTextCompare) - 1))
iInnerEnd = CLng(Right(xVal, Len(xVal) - InStrRev(xVal, "-", , vbTextCompare)))
For iInnerLoop = iInnerStart To iInnerEnd
wsNormalized.Cells(iRowIndex, 1).Value = iInnerLoop
wsNormalized.Cells(iRowIndex, 2).Value = WS.Cells(iRow, iColIndex).Value
iRowIndex = iRowIndex + 1
Next iInnerLoop
Else
wsNormalized.Cells(iRowIndex, 1).Value = WS.Cells(iRow, iCol).Value
wsNormalized.Cells(iRowIndex, 2).Value = WS.Cells(iRow, iColIndex).Value
iRowIndex = iRowIndex + 1
End If
Next iCol
SkipRow:
Next iRow
With wsNormalized
.Range("C2:C" & .Cells(.Rows.Count, "B").End(xlUp).Row).Formula = "=IFERROR(CHOOSE(MATCH(LEFT(B2,1),{""E"",""M"",""T""}),""Engine"",""Medic"",""Truck""),NA())"
.Range("C2:C" & .Cells(.Rows.Count, "B").End(xlUp).Row).Value = .Range("C2:C" & .Cells(.Rows.Count, "B").End(xlUp).Row).Value
.Cells.EntireColumn.AutoFit
End With
Call TOGGLEEVENTS(True)
End Sub
Public Sub TOGGLEEVENTS(blnState As Boolean)
Application.DisplayAlerts = blnState
Application.EnableEvents = blnState
Application.ScreenUpdating = blnState
If blnState Then Application.CutCopyMode = False
If blnState Then Application.StatusBar = False
End Sub
Function StartCol(strColumn As String) As Long
Dim wksTemp As Worksheet
On Error Resume Next
If ActiveSheet Is Nothing Then
StartCol = 0
Exit Function
End If
Set wksTemp = ActiveSheet
StartCol = wksTemp.Range(strColumn & "1").Column
On Error GoTo 0
End FunctionYou could, if you wanted, return all units in a single cell for that zone (primary response). here are a couple of UDF's designed to do so. One is designed for use with VBA (i.e. with another routine and not as a worksheet function), deftly suffixed with "VBA", and the other is designed for use as a worksheet function, deftly suffixed with "WF".
VBA version:
Function GetPrimaryUnitsVBA(vZone As Variant, _
rLookup As Range, _
Optional bEngines As Boolean = True, _
Optional bMedicUnits As Boolean = True, _
Optional bTrucks As Boolean = True) As String
'/// vZone : Zone number to look up units
'/// rLookup : Normalized range to find zone number, assumption:
'/// : col A - Zone ID
'/// : col B - Primary Unit (covering Zone)
'/// : col C - Unit Type
'/// bEngines : Include Engines in function return
'/// bMedicUnits : Include Medic units in function return
'/// bTrucks : Include Trucks in function return
Const strDelim As String = ", "
Dim wksTemp As Worksheet
Dim rngFind As Range
Dim strReturn As String
Dim varZone As Variant
Dim arrTypes() As Variant
Dim iStep As Long
On Error Resume Next
varZone = CLng(vZone)
On Error Resume Next
Set wksTemp = rLookup.Parent
arrTypes = Array("Engine", "Medic", "Truck")
For iStep = LBound(arrTypes) To UBound(arrTypes)
rLookup.AutoFilter Field:=3, Criteria1:=arrTypes(iStep)
Select Case arrTypes(iStep)
Case "Engine": If bEngines = False Then GoTo SkipUnitType
Case "Medic": If bMedicUnits = False Then GoTo SkipUnitType
Case "Truck": If bTrucks = False Then GoTo SkipUnitType
End Select
Set rngFind = Nothing
On Error Resume Next
Set rngFind = rLookup.SpecialCells(xlCellTypeVisible).Find(what:=varZone, LookAt:=xlWhole)
On Error GoTo 0
If Not rngFind Is Nothing Then
strReturn = strReturn & rngFind.Offset(0, 1).Value & strDelim
End If
SkipUnitType:
wksTemp.AutoFilterMode = False
Next iStep
If Len(strReturn) > 0 Then
GetPrimaryUnitsVBA = Left(strReturn, Len(strReturn) - Len(strDelim))
End If
End FunctionWorksheet Function version:
Function GetPrimaryUnitsWF(vZone As Variant, _
rLookup As Range, _
rReturn As Range, _
rType As Range, _
Optional bEngines As Boolean = True, _
Optional bMedicUnits As Boolean = True, _
Optional bTrucks As Boolean = True) As String
'/// vZone : Zone number to look up units
'/// rLookup : Normalized range to find zone number
'/// rReturn : Normalized range to return unit number
'/// rType : Normalized range to return type
'/// : NOTE: rLookup, rReturn and rType must match in size
'/// bEngines : Include Engines in function return
'/// bMedicUnits : Include Medic units in function return
'/// bTrucks : Include Trucks in function return
Const strDelim As String = ", "
Dim wksTemp As Worksheet
Dim rngFind As Range
Dim strReturn As String
Dim strCol As String
Dim varZone As Variant
Dim arrTypes() As Variant
Dim iStep As Long
Dim iRowStart As Long
Dim iRowEnd As Long
If rLookup.Cells.Count <> rReturn.Cells.Count Then
GetPrimaryUnitsWF = "RANGE ERROR!"
Exit Function
End If
If rLookup.Cells.Count <> rType.Cells.Count Then
GetPrimaryUnitsWF = "RANGE ERROR!"
Exit Function
End If
On Error Resume Next
varZone = CLng(vZone)
On Error Resume Next
Set wksTemp = rLookup.Parent
strCol = StringCol(rReturn.Column)
arrTypes = Array("Engine", "Medic", "Truck")
For iStep = LBound(arrTypes) To UBound(arrTypes)
Select Case arrTypes(iStep)
Case "Engine": If bEngines = False Then GoTo SkipUnitType
Case "Medic": If bMedicUnits = False Then GoTo SkipUnitType
Case "Truck": If bTrucks = False Then GoTo SkipUnitType
End Select
iRowStart = 0
iRowEnd = 0
On Error Resume Next
iRowStart = Evaluate("=MATCH(" & Chr(34) & arrTypes(iStep) & Chr(34) & ",'" & wksTemp.Name & "'!" & StringCol(rType.Column) & ",0)")
iRowEnd = Evaluate("=MATCH(" & Chr(34) & arrTypes(iStep) & Chr(34) & ",'" & wksTemp.Name & "'!" & StringCol(rType.Column) & ",1)")
On Error GoTo 0
If iRowStart = 0 Or iRowEnd = 0 Then
GoTo SkipUnitType
End If
Set rngFind = Nothing
Set rngFind = wksTemp.Range(Split(StringCol(rLookup.Column), "$")(0) & iRowStart & ":" & Split(StringCol(rLookup.Column), "$")(0) & iRowEnd).Find(what:=varZone, LookAt:=xlWhole)
If Not rngFind Is Nothing Then
strReturn = strReturn & rngFind.Offset(0, 1).Value & strDelim
End If
SkipUnitType:
Next iStep
If Len(strReturn) > 0 Then
GetPrimaryUnitsWF = Left(strReturn, Len(strReturn) - Len(strDelim))
End If
End Function
Function StringCol(lngColumn As Long) As String
Dim wksTemp As Worksheet
On Error Resume Next
If ActiveSheet Is Nothing Then
StringCol = 0
Exit Function
End If
Set wksTemp = ActiveSheet
StringCol = wksTemp.Columns(lngColumn).Address
On Error GoTo 0
End FunctionYou would call the WF version in a cell with the following formula (as an example)...
=GetPrimaryUnitsWF(B2,NormalizedZB!$A$1:$A$521,NormalizedZB!$B$1:$B$521,NormalizedZB!$C$1:$C$521)Or you could reference the entire column as such...
=GetPrimaryUnitsWF(B2,NormalizedZB!A:A,NormalizedZB!B:B,NormalizedZB!C:C)If you notice, in both functions you have the ability to refrain from identifying specific unit types (i.e. Engines), by passing a True or False (boolean) value to the appropriate arguments. Since there's no worksheet function intellisense with this type of UDF you could pass all arguments for maintainability's sake...
=GetPrimaryUnitsWF(B2,NormalizedZB!A:A,NormalizedZB!B:B,NormalizedZB!C:C,TRUE,TRUE,TRUE)This would go in the "1st Alarm Response?" column. If you used that, you could use something like this for the "Who's should it be?" column, albeit a bit long winded...
=IF(D2="No Units Established","Nobody",IF(ISERR(FIND(C2,D2)>0),MID(D2,FIND(LEFT(C2,1),D2),FIND(" "," "&SUBSTITUTE(D2,",","")&" ",FIND(LEFT(C2,1)," "&SUBSTITUTE(D2,",","")&" "))-FIND(LEFT(C2,1),SUBSTITUTE(D2,",",""))-1),C2))And a simple boolean match...
=C2=E2Or to boil down to a 1 or 0 ...
=--(C2=E2)Personally I prefer that way for easier data manipulation, plus conditional formatting is easier that way.
I do realize this is 1) a lot, and 2) not *quite* what you asked for, but hopefully it gives you options. I've made the changes to the file and have uploaded with these changes in place (on another worksheet). I've also added a PivotTable, not sure if it'll help or not. I do wonder about performance for 100k+ rows of data though. I doubt it would be better than the straight-up formula approach with the named ranges.
Another option for named ranges would be something like this (dynamic and not set via code, and assuming you have that third column like my code creates in the NormalizedZB worksheet, column C - Type)...
Engines:
=INDEX(NormalizedZB!$A:$A,MATCH("Engine",NormalizedZB!$C:$C,0),1):INDEX(NormalizedZB!$A:$A,MATCH("Engine",NormalizedZB!$C:$C,1),1)Medics:
=INDEX(NormalizedZB!$A:$A,MATCH("Medic",NormalizedZB!$C:$C,0),1):INDEX(NormalizedZB!$A:$A,MATCH("Medic",NormalizedZB!$C:$C,1),1)Trucks:
=INDEX(NormalizedZB!$A:$A,MATCH("Truck",NormalizedZB!$C:$C,0),1):INDEX(NormalizedZB!$A:$A,MATCH("Truck",NormalizedZB!$C:$C,1),1)One big downside with using functions like this (really it's just using the 1 as the third syntax in MATCH() function) is your data must be sorted (on the NormalizedZB worksheet) by column C. It shouldn't affect the other code, but that ISN'T built into any of the code either.
Okay, I think I'm done. :/
HTH
Regards,
Zack Barresse
All-Fire-Zones-Included-ZB.xlsm
ASKER
<Gasp> <Brent's jaw dropping to the ground>
I really am speechless on how this thread has progressed!
It appears that Zack is not your "Typical Fireman". Normally, you put a few firemen into a room with 2 steels balls and 1 hour later, they have broken one and lost the other.
I am going to take a hard look at this and check back later today. Thank you for taking the time to take this to another level. My original idea of taking a piece of paper and making tick marks seems to have been a bad idea <g>.
Thanks,
Brent
I really am speechless on how this thread has progressed!
It appears that Zack is not your "Typical Fireman". Normally, you put a few firemen into a room with 2 steels balls and 1 hour later, they have broken one and lost the other.
I am going to take a hard look at this and check back later today. Thank you for taking the time to take this to another level. My original idea of taking a piece of paper and making tick marks seems to have been a bad idea <g>.
Thanks,
Brent
ASKER
Hello,
I just finished up going through Zack's code. It is above my head, but I was able to follow some of it and look up a lot of the terms (ws function, UDF's and others). Even though this project has a short shelf life (We don't analyze fire zones on a daily basis, but we do look at response times all the time), the overall concept and things I learned I can use in future projects. Plus, I can pull it out the next time we are looking at zones.
I would like to show my appreciation by sending you guys (Brad, Dave and Zack) a fire department T-Shirt if that is not against any forum rules or etiquette. If you are interested in one, please send me an email (<<email address removed per site policy--byundt 5-8-12>>).
Brad, I will write something up next week with a little background description and attach a workbook with examples.
Thanks
Brent
I just finished up going through Zack's code. It is above my head, but I was able to follow some of it and look up a lot of the terms (ws function, UDF's and others). Even though this project has a short shelf life (We don't analyze fire zones on a daily basis, but we do look at response times all the time), the overall concept and things I learned I can use in future projects. Plus, I can pull it out the next time we are looking at zones.
I would like to show my appreciation by sending you guys (Brad, Dave and Zack) a fire department T-Shirt if that is not against any forum rules or etiquette. If you are interested in one, please send me an email (<<email address removed per site policy--byundt 5-8-12>>).
Brad, I will write something up next week with a little background description and attach a workbook with examples.
Thanks
Brent
ASKER
Brad,
Thanks for removing the email address and I will follow the link and fill out my profile. I'll wait to hear from the moderator. I certainly don't want to cause any problems.
Thanks,
Brent
Thanks for removing the email address and I will follow the link and fill out my profile. I'll wait to hear from the moderator. I certainly don't want to cause any problems.
Thanks,
Brent
ASKER
Just finished up a meeting and look at NFPA 1710 stuff, which included the zone project. Needless to say, everyone was very impressed. Part of the meeting made me think about taking it one step further. If a rig is not available for a call, it could mean several different things. Maybe they are at training, moved up for coverage, out of service for repairs, manpower. It's impossible to track that, but with the the information in the E column and adding dispatched and in-service time, I could at least know if they were on another incident. This would identify higher call volume areas, where more coverage may be needed.
I wrote down in sentence form my statement, although I am not sure how to nest it.
My logic says, use nested if's, vlookup, between and returns values from if statement.
=if(f2=1,"",if(f2=0,vlooku
if the value in F2 = 1, then do nothing ""
if the value in F2 = 0, then lookup E2 and does it show up in C column between (AW&AX) and (AW&AY) (The times come from the database as Date, Time, Time. I will need to concatenate the date with the times)
If it does show up, "On another incident"
if not "Other"
The range of the vlookup is what confuses me. The range will be set by the date and time parameters and I am not even sure you can do that?
Would I be on the right track? Sorry for adding on to a already completed question. Please let me know if it would be best to start a new thread and assign points. I did not know which would be best for using the thread for the knowledge base. I placed the columns in the Testdata! to help explain my thinking.
thank you
on-aonther-incident.xlsm
I wrote down in sentence form my statement, although I am not sure how to nest it.
My logic says, use nested if's, vlookup, between and returns values from if statement.
=if(f2=1,"",if(f2=0,vlooku
if the value in F2 = 1, then do nothing ""
if the value in F2 = 0, then lookup E2 and does it show up in C column between (AW&AX) and (AW&AY) (The times come from the database as Date, Time, Time. I will need to concatenate the date with the times)
If it does show up, "On another incident"
if not "Other"
The range of the vlookup is what confuses me. The range will be set by the date and time parameters and I am not even sure you can do that?
Would I be on the right track? Sorry for adding on to a already completed question. Please let me know if it would be best to start a new thread and assign points. I did not know which would be best for using the thread for the knowledge base. I placed the columns in the Testdata! to help explain my thinking.
thank you
on-aonther-incident.xlsm
ASKER
I am going to try and break this up over a few columns to see if I can figure it out, then I can try and go back to put it all together at the end. I won't check back until later tonight, so I can try and beat the solution out, which helps me learn.
thanks
thanks
Brent,
Put this in AZ2:
Copy down to the end of your range.
A large caveat of this is if, say, one engine takes multiple calls while covering for another station. This could give erroneous results. I fear a formulaic approach may be too jumbled. Would you want a VBA approach? And hey, on this board, somebody could probably prove me wrong. I just don't like mega formulas - they're a pain to maintain and troubleshoot. Call me lazy. ;)
HTH
Regards,
Zack
Put this in AZ2:
=IF(F2=1,"No",IF(SUMPRODUCT((E2=$C$2:$C$11)*(AX2>=$AX$2:$AX$11)*(AY2<=$AY$2:$AY$11),ROW($A$2:$A$11))=0,"Other","On Incident #"&INDEX($A$2:$A$10,SUMPRODUCT((E2=$C$2:$C$11)*(AX2>=$AX$2:$AX$11)*(AY2<=$AY$2:$AY$11),ROW($A$2:$A$11)),1)))Copy down to the end of your range.
A large caveat of this is if, say, one engine takes multiple calls while covering for another station. This could give erroneous results. I fear a formulaic approach may be too jumbled. Would you want a VBA approach? And hey, on this board, somebody could probably prove me wrong. I just don't like mega formulas - they're a pain to maintain and troubleshoot. Call me lazy. ;)
HTH
Regards,
Zack
Sorry, messed up the formula. The rows start on row 2, not row 1, so a very slight adjustment is needed..
HTH
Zack
=IF(F2=1,"No",IF(SUMPRODUCT((E2=$C$2:$C$11)*(AX2>=$AX$2:$AX$11)*(AY2<=$AY$2:$AY$11),ROW($A$2:$A$11))=0,"Other","On Incident #"&INDEX($A$2:$A$10,SUMPRODUCT((E2=$C$2:$C$11)*(AX2>=$AX$2:$AX$11)*(AY2<=$AY$2:$AY$11),ROW($A$2:$A$11)),1)))HTH
Zack
ASKER
Zack,
I was actually working on Sum Product as a way of working on this. Of course I was way off the mark with the solution, but at least I was in the neighborhood!
I think how I set it has a fatal flaw. I thought I should use the E column to test who should be on the call. In Row 10, TR1 is on a call and they are listed as one of the 3 possible rigs in the D11, but since E1 (Engine 1) is listed on "who should be on the call? (E11) and the logical test is testing E Column, then it miss the fact that TR1 is on a call (it is looking for E1 - Engine 1) and we get a "other" true value. Make sense?
Yes, VBA would be just fine, since I am always wanting to learn more.. Agreed on the multiple calls, but it would be a pretty limited number (say a long working fire) and even if those are missed, it would still give a decent representation of what I was thinking about.
Thanks
On-Another-Call---Ver-2.xlsm
I was actually working on Sum Product as a way of working on this. Of course I was way off the mark with the solution, but at least I was in the neighborhood!
I think how I set it has a fatal flaw. I thought I should use the E column to test who should be on the call. In Row 10, TR1 is on a call and they are listed as one of the 3 possible rigs in the D11, but since E1 (Engine 1) is listed on "who should be on the call? (E11) and the logical test is testing E Column, then it miss the fact that TR1 is on a call (it is looking for E1 - Engine 1) and we get a "other" true value. Make sense?
Yes, VBA would be just fine, since I am always wanting to learn more.. Agreed on the multiple calls, but it would be a pretty limited number (say a long working fire) and even if those are missed, it would still give a decent representation of what I was thinking about.
Thanks
On-Another-Call---Ver-2.xlsm
Brent,
You might want to change "Who's" to "Whose" -- especially if the report is being sent up the chain of command.
An on worksheet Test Data cell AZ2, you don't need to look at the entire table because it will presumably be sorted by date & time. For this reason you can use:
=IF(F2=1,"No",IF(SUMPRODUC
Note that I changed the range of the INDEX function to start in row 1. That's because you are using the ROW function to determine the row index. If you want the INDEX function to start in row 2, you need to subtract 1 from ROW. The approach shown below is a little more complex, but you can move your calculations anywhere on the worksheet and they will adapt without much thinking.
ROW($A$2:$A2)-ROW($A$2)+1
Brad
You might want to change "Who's" to "Whose" -- especially if the report is being sent up the chain of command.
An on worksheet Test Data cell AZ2, you don't need to look at the entire table because it will presumably be sorted by date & time. For this reason you can use:
=IF(F2=1,"No",IF(SUMPRODUC
Note that I changed the range of the INDEX function to start in row 1. That's because you are using the ROW function to determine the row index. If you want the INDEX function to start in row 2, you need to subtract 1 from ROW. The approach shown below is a little more complex, but you can move your calculations anywhere on the worksheet and they will adapt without much thinking.
ROW($A$2:$A2)-ROW($A$2)+1
Brad
Brent,
You don't need the ROW in the first SUMPRODUCT because you are testing whether the truck is not on another call. And you can use LOOKUP instead of INDEX in the second to eliminate the need for ROW there.
The Boolean expression in the last part of the formula is turned into a reciprocal. It will therefore return an array of either 1 or DIV/0!. By asking LOOKUP to seek 2, it ignores the error values and returns the corresponding value from column A on the same row as the last 1.
=IF(F3=1,"No",IF(SUMPRODUC
Note that this formula is for cell AZ3. The formula for AZ2 is much simpler because there are no other incidents the truck could be handling:
=IF(F2=1,"No","Other")
Brad
On-Another-Call---Ver-2.xlsm
You don't need the ROW in the first SUMPRODUCT because you are testing whether the truck is not on another call. And you can use LOOKUP instead of INDEX in the second to eliminate the need for ROW there.
The Boolean expression in the last part of the formula is turned into a reciprocal. It will therefore return an array of either 1 or DIV/0!. By asking LOOKUP to seek 2, it ignores the error values and returns the corresponding value from column A on the same row as the last 1.
=IF(F3=1,"No",IF(SUMPRODUC
Note that this formula is for cell AZ3. The formula for AZ2 is much simpler because there are no other incidents the truck could be handling:
=IF(F2=1,"No","Other")
Brad
On-Another-Call---Ver-2.xlsm
Better yet, use IFERROR to get rid of the first SUMPRODUCT altogether. Formula for AZ3:
=IFERROR(IF(F3=1,"No","On Incident #"& LOOKUP(2,1/((E3=$C$2:$C2)*
=IFERROR(IF(F3=1,"No","On Incident #"& LOOKUP(2,1/((E3=$C$2:$C2)*
And you also need to add the date and time together so you can analyze multiple days at the same time. I added two columns for this purpose. See sample workbook.
On-Another-Call---Ver-2.xlsm
On-Another-Call---Ver-2.xlsm
ASKER
Brad,
I will study this in detail in the morning and I will look at the "fatal flaw" I posted a little bit ago with Zack. I'll review the "Who's" heading. It's easy to get wrapped in the logic and forget spelling & grammar within the worksheet.
Yes, the list will be sorted by date and time.
Thanks
I will study this in detail in the morning and I will look at the "fatal flaw" I posted a little bit ago with Zack. I'll review the "Who's" heading. It's easy to get wrapped in the logic and forget spelling & grammar within the worksheet.
Yes, the list will be sorted by date and time.
Thanks
ASKER
Brad & Zack,
I took a fresh look at the the worksheet this morning and I think I figured out my problem. In the original posting we were looking at Engines, Trucks & Medics as separate categories. (Side note: Engines carry the hose and water, Trucks carry the ladders, Medics carry sick people <g>). NFPA 1710 looks at several different types of calls, two of which are FIRE CALLS and EMS CALLS. The first worksheet works perfect for fire calls. I export all Fire Calls (NIFRS Code 100) and then I export EMS Calls (NIFRS Code 300).
FIRE CALLS - 1st Engine Objective - The Fire Department shall provide for the arrival of an ENGINE company within a 320 seconds (5 min 20 seconds) to 90% of the incidents.
The overall objective looks just at, "Does the ENGINE make it in time?", but the worksheet works perfect to look at Engines, Trucks and Medics to see if they are responding to their 1st due in fire zone. This is because there is only 1 Engine, 1 Truck and 1 Medic assigned as 1st due in company to each fire zone.
EMS CALLS - 1st Arrival Objective - The Fire Department shall provide for the arrival of a 1st responding UNIT within 300 Seconds (5 Min) to 90% of the incidents.
The objective doesn't care if you are a Engine, Truck or Medic Unit, it just wants to know if one of the units makes it in time. The program we use (Vinelight) looks at these parameters and returns a true or false if the objective was met. I attached a sample worksheet to show the export I pull from vinelight (It comes in text format and I change the date and time into values).
Instead of looking at the Engine, Trucks and Medics as individuals on EMS CALLS we look at Engines and Trucks as a group and Medics by themselves. This is because on a EMS call there are only 2 unit dispatched , either a truck or engine and a medic unit. The medics are the same, it still is just looking at Medics and will return the correct value. The trouble is with the Trucks and Engines. In fire zone 101 it can be either E3 or TR31, there is no longer a unique value between the engine and truck.
I believe the fire calls will not be an issue. I am going to download some fire calls and run a test.
With the EMS calls, instead of the lookup vector looking at the E column, I am going to see if it can look at the D column instead and still work.
1/((E5=$C$2:$C4)*(AZ5>=$AZ$2:$AZ
Sorry this turned into a confusing mess. I hope this explains a little more.
Thanks
lookup vector.
Look-at-Incidents.xlsm
I took a fresh look at the the worksheet this morning and I think I figured out my problem. In the original posting we were looking at Engines, Trucks & Medics as separate categories. (Side note: Engines carry the hose and water, Trucks carry the ladders, Medics carry sick people <g>). NFPA 1710 looks at several different types of calls, two of which are FIRE CALLS and EMS CALLS. The first worksheet works perfect for fire calls. I export all Fire Calls (NIFRS Code 100) and then I export EMS Calls (NIFRS Code 300).
FIRE CALLS - 1st Engine Objective - The Fire Department shall provide for the arrival of an ENGINE company within a 320 seconds (5 min 20 seconds) to 90% of the incidents.
The overall objective looks just at, "Does the ENGINE make it in time?", but the worksheet works perfect to look at Engines, Trucks and Medics to see if they are responding to their 1st due in fire zone. This is because there is only 1 Engine, 1 Truck and 1 Medic assigned as 1st due in company to each fire zone.
EMS CALLS - 1st Arrival Objective - The Fire Department shall provide for the arrival of a 1st responding UNIT within 300 Seconds (5 Min) to 90% of the incidents.
The objective doesn't care if you are a Engine, Truck or Medic Unit, it just wants to know if one of the units makes it in time. The program we use (Vinelight) looks at these parameters and returns a true or false if the objective was met. I attached a sample worksheet to show the export I pull from vinelight (It comes in text format and I change the date and time into values).
Instead of looking at the Engine, Trucks and Medics as individuals on EMS CALLS we look at Engines and Trucks as a group and Medics by themselves. This is because on a EMS call there are only 2 unit dispatched , either a truck or engine and a medic unit. The medics are the same, it still is just looking at Medics and will return the correct value. The trouble is with the Trucks and Engines. In fire zone 101 it can be either E3 or TR31, there is no longer a unique value between the engine and truck.
I believe the fire calls will not be an issue. I am going to download some fire calls and run a test.
With the EMS calls, instead of the lookup vector looking at the E column, I am going to see if it can look at the D column instead and still work.
1/((E5=$C$2:$C4)*(AZ5>=$AZ$2:$AZ
Sorry this turned into a confusing mess. I hope this explains a little more.
Thanks
lookup vector.
Look-at-Incidents.xlsm
ASKER
I have been studying this and I get all of it except the for the LOOKUP(2,1/(.
I get that the lookup vector is looking for a match between the time values, but I don't get 2,1/ . What does the 2 represent and why does it divide itself by 1? I know the 2 is referring to value in the E column, just not sure how the 2 points to it.
I see the =iferror and that is why I get the "other" because it can't find a match.
then the next if statement is yes or no. If it =1, then returns a "No"
If false, it goes into the lookup. The lookup is matching the value in E with anything between time values. Then the result vector returns the incident #.
Thanks
I get that the lookup vector is looking for a match between the time values, but I don't get 2,1/ . What does the 2 represent and why does it divide itself by 1? I know the 2 is referring to value in the E column, just not sure how the 2 points to it.
I see the =iferror and that is why I get the "other" because it can't find a match.
then the next if statement is yes or no. If it =1, then returns a "No"
If false, it goes into the lookup. The lookup is matching the value in E with anything between time values. Then the result vector returns the incident #.
Thanks
Brent,
A few things to know about LOOKUP:
If the first parameter is "zzzzz", that will probably alphabetize after any text in the lookup vector. So the last bit of text in the lookup vector is trapped, and returned.
If the first parameter is a whopping large number (my favorite is 1E+40, or 10^40), then lookup traps the last number in the lookup vector.
In the case of this particular formula, 1/((Boolean expression)*(Boolean expression)) returns an array of either 1/1 or 1/0. Taking the reciprocal means that the formula ignores any rows that don't satisfy all criteria (feature #1).
If the function is looking for 2, it will never find any number bigger. So the last number is trapped (feature #6). Since this formula is using LOOKUP with three parameters, it returns the corresponding value from column A.
Unless, of course, the truck, engine or medic wasn't on another call--so the lookup vector is all error values. In which case LOOKUP returns an error value (feature #5), IFERROR comes into play, and the formula returns "Other".
Brad
A few things to know about LOOKUP:
1) It tolerates error values.
2) In fact, it ignores any value that isn't the same type as the first parameter.
3) It expects the lookup vector to be sorted in ascending order. It then stops searching the first time the lookup value exceeds a value in the lookup vector.
4) It either takes two or three parameters. If given two parameters, it returns a value from the last column of the range in the second parameter on the same row as identified in feature #3 above. If given three parameters, it returns a corresponding value from the range in the third parameter.
5) If no values of the same type as first parameter are found in the lookup vector, LOOKUP returns an error value.
6) If no value in lookup vector is larger than the first parameter, the matching row is the last one with the same data type.
2) In fact, it ignores any value that isn't the same type as the first parameter.
3) It expects the lookup vector to be sorted in ascending order. It then stops searching the first time the lookup value exceeds a value in the lookup vector.
4) It either takes two or three parameters. If given two parameters, it returns a value from the last column of the range in the second parameter on the same row as identified in feature #3 above. If given three parameters, it returns a corresponding value from the range in the third parameter.
5) If no values of the same type as first parameter are found in the lookup vector, LOOKUP returns an error value.
6) If no value in lookup vector is larger than the first parameter, the matching row is the last one with the same data type.
If the first parameter is "zzzzz", that will probably alphabetize after any text in the lookup vector. So the last bit of text in the lookup vector is trapped, and returned.
If the first parameter is a whopping large number (my favorite is 1E+40, or 10^40), then lookup traps the last number in the lookup vector.
In the case of this particular formula, 1/((Boolean expression)*(Boolean expression)) returns an array of either 1/1 or 1/0. Taking the reciprocal means that the formula ignores any rows that don't satisfy all criteria (feature #1).
If the function is looking for 2, it will never find any number bigger. So the last number is trapped (feature #6). Since this formula is using LOOKUP with three parameters, it returns the corresponding value from column A.
Unless, of course, the truck, engine or medic wasn't on another call--so the lookup vector is all error values. In which case LOOKUP returns an error value (feature #5), IFERROR comes into play, and the formula returns "Other".
Brad
ASKER
Brad,
That makes much more sense, thanks. I"ll go back and look at it again.
Thanks
Brent
That makes much more sense, thanks. I"ll go back and look at it again.
Thanks
Brent
Brent,
Regarding feature #6, I should have given you some examples:
6) If no value in lookup vector is larger than the first parameter, the matching row is the last one with the same data type.
You want the last number in column A?
=LOOKUP(1E+40,A:A)
You want the last text?
=LOOKUP("zzzzz",A:A)
You want a value from column C on same row as last number in column A? You can use either the two parameter or three parameter form of the function:
=LOOKUP(1E+40,A:C)
=LOOKUP(1E+40,A:A,C:C)
Brad
Regarding feature #6, I should have given you some examples:
6) If no value in lookup vector is larger than the first parameter, the matching row is the last one with the same data type.
You want the last number in column A?
=LOOKUP(1E+40,A:A)
You want the last text?
=LOOKUP("zzzzz",A:A)
You want a value from column C on same row as last number in column A? You can use either the two parameter or three parameter form of the function:
=LOOKUP(1E+40,A:C)
=LOOKUP(1E+40,A:A,C:C)
Brad
ASKER
Brad,
I'll take a fresh look at this in the morning with all the details you added with the lookup.
Thanks,
Brent
I'll take a fresh look at this in the morning with all the details you added with the lookup.
Thanks,
Brent
ASKER
Zack and Brad,
I have been thinking about this the past couple of days and I had a thought.
1. The problem is the E Column is only looking for 1 value. It will be either a TRUCK, ENGINE or MEDIC. That works for Fire, but not EMS. Since both Fire an EMS will be combined into this worksheet it will need to look at both at the same time. EMS calls will be dispatched as either 1 Engine or 1 Truck AND a medic unit. So, there really is 2 components to look at.
2. Instead of having 1 value in the E Column, what if we grouped the Engine/Truck into one column and the Medic into another?
3. Then, If the value in C is an E or T, then go to the E Column, if the value in C is a M, then go to the F column
Brad's normalization had 3 ranges, so instead of 3 ranges, have 1 range for Engine/Truck and 1 Range for Medic. Once the list is normalized, it will probably stay that way for quite some time. We normally don't change zones, since it requires quite a bit of work with 911.
Thanks,
Brent
EE-Follow-up.xlsm
I have been thinking about this the past couple of days and I had a thought.
1. The problem is the E Column is only looking for 1 value. It will be either a TRUCK, ENGINE or MEDIC. That works for Fire, but not EMS. Since both Fire an EMS will be combined into this worksheet it will need to look at both at the same time. EMS calls will be dispatched as either 1 Engine or 1 Truck AND a medic unit. So, there really is 2 components to look at.
2. Instead of having 1 value in the E Column, what if we grouped the Engine/Truck into one column and the Medic into another?
3. Then, If the value in C is an E or T, then go to the E Column, if the value in C is a M, then go to the F column
Brad's normalization had 3 ranges, so instead of 3 ranges, have 1 range for Engine/Truck and 1 Range for Medic. Once the list is normalized, it will probably stay that way for quite some time. We normally don't change zones, since it requires quite a bit of work with 911.
Thanks,
Brent
EE-Follow-up.xlsm
Brent,
VLOOKUP returns only one value, so if you want both an engine and a truck you need either a different approach (with a single two-column list) or three lists.
I put my Normalizer sub back in your sample workbook and got an error with Fire Zones cell Q29, which appears to be showing a range of values 312 to 316. To remedy the problem, it is better to list each zone in a separate cell in the table.
I could now return the medic or engine & truck that should have responded to the call with this formula. It uses the medics2 list when a medic responded to the call, or else concatenates the results from engines2 and trucks2 lists when an engine or truck responded. All simple VLOOKUP functions. I put the formula in column G, which I inserted.
=IF(LEFT(C2,1)="M",VLOOKUP
And to see whether the right engine/truck/medic did respond, I needed a somewhat more complicated formula. By putting a comma after the responder in C2 and after the expected responders in G2, I could use the SEARCH function to see if there was a match.
=IF(ISERROR(SEARCH(C2 & ",",G2 & ",")),0%,100%)
The 0% and 100% were to dovetail with your Conditional Formatting.
Brad
EE-Follow-up-1.xlsm
VLOOKUP returns only one value, so if you want both an engine and a truck you need either a different approach (with a single two-column list) or three lists.
I put my Normalizer sub back in your sample workbook and got an error with Fire Zones cell Q29, which appears to be showing a range of values 312 to 316. To remedy the problem, it is better to list each zone in a separate cell in the table.
I could now return the medic or engine & truck that should have responded to the call with this formula. It uses the medics2 list when a medic responded to the call, or else concatenates the results from engines2 and trucks2 lists when an engine or truck responded. All simple VLOOKUP functions. I put the formula in column G, which I inserted.
=IF(LEFT(C2,1)="M",VLOOKUP
And to see whether the right engine/truck/medic did respond, I needed a somewhat more complicated formula. By putting a comma after the responder in C2 and after the expected responders in G2, I could use the SEARCH function to see if there was a match.
=IF(ISERROR(SEARCH(C2 & ",",G2 & ",")),0%,100%)
The 0% and 100% were to dovetail with your Conditional Formatting.
Brad
EE-Follow-up-1.xlsm
ASKER
I thought we would need to go back to your ranges, but I wasn't sure. I am going to study this first thing in the morning and I'll post back.
Thanks for taking the time to work on this, it is probably the single most important thing I need to track with all of my statistics. it might sound a little hokey, but I think Zack would agree, that this type of information that will save lives. It allows fire departments to fine tune the placement of rigs, which in turn provides faster response times for fire and medical calls.
Thanks,
Brent
Thanks for taking the time to work on this, it is probably the single most important thing I need to track with all of my statistics. it might sound a little hokey, but I think Zack would agree, that this type of information that will save lives. It allows fire departments to fine tune the placement of rigs, which in turn provides faster response times for fire and medical calls.
Thanks,
Brent
ASKER
Brad,
It actually makes sense to me! It takes me a minute to organize my thoughts on the Lookup and the 6 things you listed, but @ the end of it I can see what you did. This works perfect and it closes the book on this thread. I will go through it in detail tomorrow, clean everything up and post the final book with a summary. Thanks for your time and effort on this extra step.
Thanks,
Brent
It actually makes sense to me! It takes me a minute to organize my thoughts on the Lookup and the 6 things you listed, but @ the end of it I can see what you did. This works perfect and it closes the book on this thread. I will go through it in detail tomorrow, clean everything up and post the final book with a summary. Thanks for your time and effort on this extra step.
Thanks,
Brent
ASKER
Brad,
I did further testing and came up with a question. I attached a workbook with some text boxes. I completely understand if you want to wash your hands of this project, so please do not respond if you are done with it. I can start over with a different approach to my problem to identify with a rig is avaiable for a call because of 1. They are on another incident already 2. or the only other explantion would be they are out of service (training, ect)
Basically, the trouble comes from when a Unit is available for their zone (meaning that they do no show up in C column during the time from of the call (BB:BC).
Row 13 would be my example. E56 is listed in C13 for which E65 and TR61 are listed as primary for the zone (E13-same thing as G13). Neither T65 or TR61 show up in the C column during the time frame (BB:BC) that would list them as on "other incident". Since they don't show up in C column, it would be "out of service".
I think it would be an if statement, =if(G13<>LOOKUP(2,1/((E13=
Thanks
out-of-service--or--on-another-i.xlsm
I did further testing and came up with a question. I attached a workbook with some text boxes. I completely understand if you want to wash your hands of this project, so please do not respond if you are done with it. I can start over with a different approach to my problem to identify with a rig is avaiable for a call because of 1. They are on another incident already 2. or the only other explantion would be they are out of service (training, ect)
Basically, the trouble comes from when a Unit is available for their zone (meaning that they do no show up in C column during the time from of the call (BB:BC).
Row 13 would be my example. E56 is listed in C13 for which E65 and TR61 are listed as primary for the zone (E13-same thing as G13). Neither T65 or TR61 show up in the C column during the time frame (BB:BC) that would list them as on "other incident". Since they don't show up in C column, it would be "out of service".
I think it would be an if statement, =if(G13<>LOOKUP(2,1/((E13=
Thanks
out-of-service--or--on-another-i.xlsm
Brent,
If I understand correctly, only a medic may respond to an EMS event, but either an engine or a truck may respond to a fire. If so, you need to check whether both of them are tied up elsewhere. For this reason, I decided to use two columns to list who should respond: one for the first vehicle and the second for the truck in the event of a fire. I used columns E and F for this purpose. Their formulas are:
=IF(LEFT(C2,1)="M",VLOOKUP
=IF(LEFT(C2,1)="M","",VLOO
To decide whether the right vehicle responded (column H), I can check for both engine (or medic) and truck possibilities with:
=IF(COUNTIF(E2:F2,C2),100%
And to decide whether the primary responders were otherwise occupied, I used potentially two lines (to check on both engine and truck) in column G with:
=IFERROR(IF(H2=1,"",E2 & " On Incident #"&LOOKUP(2,1/((E2=$C1:$C$
The column G formula is really just the same as the previous LOOKUP except that there are two LOOKUP calls: one for the first line and potentially another in a text wrapped second line.
CHAR(10) returns the line feed character (equivalent to ALT + Enter)
Note that the second LOOKUP is contained within an IF that tests whether F2 is blank (EMS call, so medic responded) or the right vehicle responded.
Brad
out-of-service--or--on-another-i.xlsm
If I understand correctly, only a medic may respond to an EMS event, but either an engine or a truck may respond to a fire. If so, you need to check whether both of them are tied up elsewhere. For this reason, I decided to use two columns to list who should respond: one for the first vehicle and the second for the truck in the event of a fire. I used columns E and F for this purpose. Their formulas are:
=IF(LEFT(C2,1)="M",VLOOKUP
=IF(LEFT(C2,1)="M","",VLOO
To decide whether the right vehicle responded (column H), I can check for both engine (or medic) and truck possibilities with:
=IF(COUNTIF(E2:F2,C2),100%
And to decide whether the primary responders were otherwise occupied, I used potentially two lines (to check on both engine and truck) in column G with:
=IFERROR(IF(H2=1,"",E2 & " On Incident #"&LOOKUP(2,1/((E2=$C1:$C$
The column G formula is really just the same as the previous LOOKUP except that there are two LOOKUP calls: one for the first line and potentially another in a text wrapped second line.
CHAR(10) returns the line feed character (equivalent to ALT + Enter)
Note that the second LOOKUP is contained within an IF that tests whether F2 is blank (EMS call, so medic responded) or the right vehicle responded.
Brad
out-of-service--or--on-another-i.xlsm
ASKER
Brad,
The first sentence is not quite right, but your solution (I think) is the same nevertheless.
On an EMS call a Medic will respond with a (Truck) OR (Engine). On a Fire call, 1 Engine, 1 Truck and 1 Medic will respond (actually more respond, but I am only testing the 1st due in Medic, Engine and Truck). But, like I said, the solution is still the same because you tested for all three of them with your formulas. If it sees the Engine, it check to see if it the right Engine responded and then it check's for the truck. If it sees a Medic, then it checks to see if the right Medic responded and then it checks for the truck. Either way, all three possibilities (that will show up in the C Column) have been checked.
I will take a very close look at the formulas and run a few thousand actual calls to get a better idea how it works.
Thanks for taking the time to look at this again,
Brent
The first sentence is not quite right, but your solution (I think) is the same nevertheless.
On an EMS call a Medic will respond with a (Truck) OR (Engine). On a Fire call, 1 Engine, 1 Truck and 1 Medic will respond (actually more respond, but I am only testing the 1st due in Medic, Engine and Truck). But, like I said, the solution is still the same because you tested for all three of them with your formulas. If it sees the Engine, it check to see if it the right Engine responded and then it check's for the truck. If it sees a Medic, then it checks to see if the right Medic responded and then it checks for the truck. Either way, all three possibilities (that will show up in the C Column) have been checked.
I will take a very close look at the formulas and run a few thousand actual calls to get a better idea how it works.
Thanks for taking the time to look at this again,
Brent
Brent,
Based on your latest description, I suggest having three columns for the expected responders: one for engines, trucks and medics. Armed with this information, you can then use a simple COUNTIF to see if the first responder was one of the expected ones. And if not, you can search to see if the expected engine/truck/medic was on duty elsewhere.
=VLOOKUP(--B2,engines2,2,F
=IF(COUNTIF(E2:G2,C2),100%
The final formula (to determine status of all three expected vehicles) is a concatenation of three LOOKUP formulas:
=IFERROR(IF(I2=1,"",E2 & " On Incident #"&LOOKUP(2,1/((E2=$C1:$C$
Brad
out-of-service--or--on-another-i.xlsm
Based on your latest description, I suggest having three columns for the expected responders: one for engines, trucks and medics. Armed with this information, you can then use a simple COUNTIF to see if the first responder was one of the expected ones. And if not, you can search to see if the expected engine/truck/medic was on duty elsewhere.
=VLOOKUP(--B2,engines2,2,F
=IF(COUNTIF(E2:G2,C2),100%
The final formula (to determine status of all three expected vehicles) is a concatenation of three LOOKUP formulas:
=IFERROR(IF(I2=1,"",E2 & " On Incident #"&LOOKUP(2,1/((E2=$C1:$C$
Brad
out-of-service--or--on-another-i.xlsm
ASKER
Brad,
I just finished up a test run of a month of data and it works perfect. I copied/pasted the data into the worksheet, then copied the formulas down, then I used "Text to Column" to separate the H column into 3 columns. That allowed me to place it in a pivot table, filter by Engines, Trucks and Medics by many different situations. I will now create some macros to asked questions like: "Which Engine?" or " Which Truck?" or "What Zone?"
I will clean all of this up and post a workbook next week. Thank you for your time and effort with this project.
thanks
Brent
I just finished up a test run of a month of data and it works perfect. I copied/pasted the data into the worksheet, then copied the formulas down, then I used "Text to Column" to separate the H column into 3 columns. That allowed me to place it in a pivot table, filter by Engines, Trucks and Medics by many different situations. I will now create some macros to asked questions like: "Which Engine?" or " Which Truck?" or "What Zone?"
I will clean all of this up and post a workbook next week. Thank you for your time and effort with this project.
thanks
Brent
ASKER
Hello,
I have just finished cleaning up the final workbook for the thread and I wanted to post a quick summary. The thread's topic started off with a question about returning a value from a poorly designed table, but ended up covering normalization, several formulas and a few macro's. We had 3 experts whose responses were all correct and effective, but arrived at a solution with different approaches.
The topic of the workbook dealt with NFPA 1710: STANDARD FOR THE ORGANIZATION AND DEPLOYMENT OF FIRE SUPPRESSION OPERATIONS, EMERGENCY MEDICAL OPERATIONS, AND SPECIAL OPERATIONS TO THE PUBLIC BY CAREER FIRE DEPARTMENTS which tracks several different time periods in an emergency response. At first, I was just looking at, "If a unit (Truck, Engine, Medic) responded to their territory." It turned out that not only was I able to track, "If they responded to their own territory?", but if they didn't, "Why not?".
I have included 3 worksheets:
1. Test Data
2. Mock Fire Zones (geographical locations)
3. Normalization (Brad's Method)
I would like to thank Brad, Zack and Dave for taking so much time to create a very helpful workbook.
I left in Zack's macros in case anyone wanted to follow his method of normalization.
Thanks,
Brent
I have just finished cleaning up the final workbook for the thread and I wanted to post a quick summary. The thread's topic started off with a question about returning a value from a poorly designed table, but ended up covering normalization, several formulas and a few macro's. We had 3 experts whose responses were all correct and effective, but arrived at a solution with different approaches.
The topic of the workbook dealt with NFPA 1710: STANDARD FOR THE ORGANIZATION AND DEPLOYMENT OF FIRE SUPPRESSION OPERATIONS, EMERGENCY MEDICAL OPERATIONS, AND SPECIAL OPERATIONS TO THE PUBLIC BY CAREER FIRE DEPARTMENTS which tracks several different time periods in an emergency response. At first, I was just looking at, "If a unit (Truck, Engine, Medic) responded to their territory." It turned out that not only was I able to track, "If they responded to their own territory?", but if they didn't, "Why not?".
I have included 3 worksheets:
1. Test Data
2. Mock Fire Zones (geographical locations)
3. Normalization (Brad's Method)
I would like to thank Brad, Zack and Dave for taking so much time to create a very helpful workbook.
I left in Zack's macros in case anyone wanted to follow his method of normalization.
Thanks,
Brent
Brent,
I moved part of your background material from your last Comment (second paragraph) to the original question. I think it sets the stage better for anybody searching the Experts Exchange database.
Did you mean to attach a workbook?
Brad
I moved part of your background material from your last Comment (second paragraph) to the original question. I think it sets the stage better for anybody searching the Experts Exchange database.
Did you mean to attach a workbook?
Brad
ASKER
Brad,
OK, thanks for cleaning up the comment. Yes, I wanted to attach, but I always forget the 2nd step.
Here is the workbook.
Thanks,
Brent
EE-Final-NFPA-1710-Final-Workboo.xlsm
OK, thanks for cleaning up the comment. Yes, I wanted to attach, but I always forget the 2nd step.
Here is the workbook.
Thanks,
Brent
EE-Final-NFPA-1710-Final-Workboo.xlsm
Brent,
Thanks for posting the final workbook. It makes a complete package when somebody is looking through the Experts Exchange database for a solution to their NFPA 1710 problem.
If you don't mind, how long does it take to run through a full set of data? And how many rows was that? There is frequently a bit of a tussle between the Access jocks who think that any database question calls for Access, and the Excel people who charge full speed ahead regardless. It's good in such cases to have a few facts and figures at your fingertips.
Brad
Thanks for posting the final workbook. It makes a complete package when somebody is looking through the Experts Exchange database for a solution to their NFPA 1710 problem.
If you don't mind, how long does it take to run through a full set of data? And how many rows was that? There is frequently a bit of a tussle between the Access jocks who think that any database question calls for Access, and the Excel people who charge full speed ahead regardless. It's good in such cases to have a few facts and figures at your fingertips.
Brad
ASKER
Brad,
I ran just a little over 250,000 rows and it was completed almost instantly. By the time I hit CRL+SHIFT+down arrow, it had already completed every row. One of the first suggestions someone gave me was to put it into a database. That might be a very good solution, but I am putting my efforts towards learning Excel, so it just made sense to me to try and work out the solution in Excel. Zack has also introduced me to Power Pivot, so that is just one more reason to push me towards Excel, rather than Access.
As far as NFPA 1710, this is just the tip of the iceberg in what can be analyzed with Excel.
Thanks,
Brent
I ran just a little over 250,000 rows and it was completed almost instantly. By the time I hit CRL+SHIFT+down arrow, it had already completed every row. One of the first suggestions someone gave me was to put it into a database. That might be a very good solution, but I am putting my efforts towards learning Excel, so it just made sense to me to try and work out the solution in Excel. Zack has also introduced me to Power Pivot, so that is just one more reason to push me towards Excel, rather than Access.
As far as NFPA 1710, this is just the tip of the iceberg in what can be analyzed with Excel.
Thanks,
Brent
ASKER
Brad,
I thought I would give an update to this project and also a few more details for anyone who follows the thread.
I was able to collect all the statistics from our Engines, Medics and Trucks from 2009 through the present and run them through the worksheet. Initially I thought I would be able to keep them all in one worksheet, but Excel kept crashing with the amount of work to complete the formulas. Instead I broke it up into quarters, which worked just fine. I turned off the Automatic Formulas in options until I was able to copy and paste all the information into the worksheet. Once I turned it back on, the formulas ran without difficulty. I then copied and pasted special (values) from each quarter into my "main table". I then separated the Which Engine, Which Truck and Which Medic into their own columns using text to column function. In the end I believe I had over 300,000 rows in my table. I was able to create pivot tables and charts and analyzed the data to help improve the assignment of fire zones to different rigs. Not only was it beneficial to NFPA 1710, it reinforced the proper placement of rigs throughout the city. It is another tool, along with GIS, to help identify trouble spots with the placement of rigs in our fire zones.
Again, thanks for all the work you put into the project, it is being used wisely <g>
Brent
I thought I would give an update to this project and also a few more details for anyone who follows the thread.
I was able to collect all the statistics from our Engines, Medics and Trucks from 2009 through the present and run them through the worksheet. Initially I thought I would be able to keep them all in one worksheet, but Excel kept crashing with the amount of work to complete the formulas. Instead I broke it up into quarters, which worked just fine. I turned off the Automatic Formulas in options until I was able to copy and paste all the information into the worksheet. Once I turned it back on, the formulas ran without difficulty. I then copied and pasted special (values) from each quarter into my "main table". I then separated the Which Engine, Which Truck and Which Medic into their own columns using text to column function. In the end I believe I had over 300,000 rows in my table. I was able to create pivot tables and charts and analyzed the data to help improve the assignment of fire zones to different rigs. Not only was it beneficial to NFPA 1710, it reinforced the proper placement of rigs throughout the city. It is another tool, along with GIS, to help identify trouble spots with the placement of rigs in our fire zones.
Again, thanks for all the work you put into the project, it is being used wisely <g>
Brent
Brent,
I'm glad to hear that you were able to put the discussion in this thread to such good results.
Would you be permitted to post the workbook showing your PowerPivot results? I'm thinking that someone searching the Experts-Exchange database for NFPA 1710 would find that of interest.
At one time, Experts Exchange had a "Question of the Year" contest. Criteria aren't defined, but nominees should highlight the value of the site and show its Experts working at their best on a problem of some significance. In my opinion, this thread would be a good candidate.
Brad
I'm glad to hear that you were able to put the discussion in this thread to such good results.
Would you be permitted to post the workbook showing your PowerPivot results? I'm thinking that someone searching the Experts-Exchange database for NFPA 1710 would find that of interest.
At one time, Experts Exchange had a "Question of the Year" contest. Criteria aren't defined, but nominees should highlight the value of the site and show its Experts working at their best on a problem of some significance. In my opinion, this thread would be a good candidate.
Brad
ASKER
Brad,
I would not be allowed to post actual results, but I could dummy up a reflection of what the project can accomplish if that would be helpful? Geographic Information System (GIS) is more likely what departments use to track calls for locations and volume, but it is time consuming and involves a lot of map skills. Although this project is still in development and I would not call it user friendly (not the excel portion, but more so the extraction and input of data from the SQL database), but every little bit helps and I am hopeful I am allowed to continue with its progress.
Thanks,
Brent
I would not be allowed to post actual results, but I could dummy up a reflection of what the project can accomplish if that would be helpful? Geographic Information System (GIS) is more likely what departments use to track calls for locations and volume, but it is time consuming and involves a lot of map skills. Although this project is still in development and I would not call it user friendly (not the excel portion, but more so the extraction and input of data from the SQL database), but every little bit helps and I am hopeful I am allowed to continue with its progress.
Thanks,
Brent
ASKER
Brad,
I contacted customer support to ask about submitting a story with this thread. I am not sure if there is a thread of the year, but I would guess this one would be a good story. I have continued to use your formulas to track fire department assets. I also have continued my Excel education to include VBA and charting. I am still learning different ways to assess the results from the thread. Although I am limited on specifics, I can share that the formulas, along with our NFPA 1710 software are a valuable part of evaluating our services to the citizens.
Thanks,
Brent
I contacted customer support to ask about submitting a story with this thread. I am not sure if there is a thread of the year, but I would guess this one would be a good story. I have continued to use your formulas to track fire department assets. I also have continued my Excel education to include VBA and charting. I am still learning different ways to assess the results from the thread. Although I am limited on specifics, I can share that the formulas, along with our NFPA 1710 software are a valuable part of evaluating our services to the citizens.
Thanks,
Brent
ASKER
Sounds good and I will type it up over this next week. Thanks for the response. All 3 were helpful and I had a chance to work with Brad and Zack in detail while discovering more complexity to the problem than I first anticipated. Their response went above and beyond what anyone could expect from a forum question.
Thanks,
Brent
Thanks,
Brent
*smile*
Their response went above and beyond what anyone could expect from a forum question.
That's why old timers like Brad and me don't think of EE as a forum. We think of it as a community, and just as your job (okay, maybe not just like your job) involves helping others and taking care of your community, that's what we do at Experts Exchange.
On behalf of all of the people who help out here, thank you for your very kind words; it really is what keeps all of us coming back.
ep
Their response went above and beyond what anyone could expect from a forum question.
That's why old timers like Brad and me don't think of EE as a forum. We think of it as a community, and just as your job (okay, maybe not just like your job) involves helping others and taking care of your community, that's what we do at Experts Exchange.
On behalf of all of the people who help out here, thank you for your very kind words; it really is what keeps all of us coming back.
ep
MS Access is one good option.