Find entry based on conditions
Hello Oscar,
I am building a spreadsheet for tracking calls for my local fire department. I have a column "a" as an incident number. the incident number is a one-time yearly number usage.
Column "c" is apparatus name and there are 1 of 8 possible names may be used in this cell. column "h" has the formula to give me the time spent on the scene.
I am needing help getting sheet 2 to tag the time spent on a call per apparatus. Sheet 2 is the names of personnel on the scene. I want to put the time on scene according to what apparatus they were on for each incident.
example:
column "a" newest entry is #10
column "c" is "bt1" or "bt2" or "e1" or "e3" or "e4" or "e5" or "pov" or "stby"
There often will be multiple rows with the same incident# in column "a" but different apparatus in column "c".
Column "h" will have on scene time calculated by "=f5-d5"(for that row)
I need to tag on the scene time from sheet 1 column "h" to the corresponding incident number column "a" according to the apparatus column "c".
Last Total
Enroute Arrival Clear Response Incident
Incident # Date Apparatus Time Time Time Time Time
1 03/01/12 bt2 8:18 8:27 18:45 0:09:00 10:27:00
2 03/25/12 bt2 8:20 8:23 17:45 0:03:00 9:25:00
e1 17:05 17:10 17:45 0:05:00 0:40:00
e3 12:33 12:38 17:45 0:05:00 5:12:00
3 03/26/12 e4 7:45 8:08 10:22 0:23:00 2:37:00
4 03/26/12 bt2 11:14 11:16 11:29 0:02:00 0:15:00
5 03/27/12 pov 13:10 13:20 18:36 0:10:00 5:26:00
stby 13:15 13:20 18:36 0:05:00 5:21:00
bt1 13:15 13:20 18:36 0:05:00 5:21:00
bt2 13:16 13:21 18:36 0:05:00 5:20:00
6 03/28/12 e1 8:18 8:27 18:45 0:09:00 10:27:00
e3 8:20 8:30 18:45 0:10:00 10:25:00
7 03/28/12 bt1 8:20 8:23 17:45 0:03:00 9:25:00
e5 9:00 9:03 17:45 0:03:00 8:45:00
8 03/28/12 bt2 9:20 9:22 9:59 0:02:00 0:39:00
9 03/29/12 e1 17:45 17:50 18:00 0:05:00 0:15:00
The array formula reads incident numbers and apparatus values, therefore, I entered missing incident numbers.
How to enter a formula in blank cells
- Select cell range A1:A17
- Press F5
- Press with left mouse button on "Special"
- Press with left mouse button on "Blanks"
- Press with left mouse button on "OK"
- Type =A3
- Press and hold Ctrl
- Press Enter
Array formula in sheet2
Array formula in cell C2:
How to enter an array formula
- Double press with left mouse button on cell C2
- Copy/Paste array formula
- Press and hold Ctrl and Shift
- Press Enter
How to copy array formula
- Select cell C2
- Copy (Ctrl + c)
- Select cell range C3:C10
- Paste (Ctrl + v)
Explaining array formula in cell C2
Step 1 - Find incident number and Apparatus value
(B2=Sheet1!$C$2:$C$17)*(A2=Sheet1!$A$2:$A$17)
becomes
("bt2"={"bt2";"bt2";"e1";"e3";"e4";"bt2";"pov";"stby";"bt1";"bt2";"e1";"e3";"bt1";"e5";"bt2";"e1"} ) * (1={1;2;2;2;3;4;5;5;5;5;6;6;7;7;8;9})
becomes
{1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}
Step 2 - Convert array to row numbers
IF((B2=Sheet1!$C$2:$C$17)*(A2=Sheet1!$A$2:$A$17), MATCH(ROW(Sheet1!$A$2:$A$17), ROW(Sheet1!$A$2:$A$17)), "")
becomes
IF({1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}, MATCH(ROW(Sheet1!$A$2:$A$17), ROW(Sheet1!$A$2:$A$17)), "")
becomes
IF({1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}, {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17}, "")
and returns
{1;"";"";"";"";"";"";"";"";"";"";"";"";"";"";""}
Step 3 - Find smallest value in array
MIN(IF((B2=Sheet1!$C$2:$C$17)*(A2=Sheet1!$A$2:$A$17), MATCH(ROW(Sheet1!$A$2:$A$17), ROW(Sheet1!$A$2:$A$17)), ""))
becomes
MIN({1;"";"";"";"";"";"";"";"";"";"";"";"";"";"";""})
and returns
1
Step 4 - Return an error if no value is found
IF(SUM(--(B2=Sheet1!$C$2:$C$17)*(A2=Sheet1!$A$2:$A$17))=0, NA(), MIN(IF((B2=Sheet1!$C$2:$C$17)*(A2=Sheet1!$A$2:$A$17), MATCH(ROW(Sheet1!$A$2:$A$17), ROW(Sheet1!$A$2:$A$17)), "")))
becomes
IF(SUM(--(B2=Sheet1!$C$2:$C$17)*(A2=Sheet1!$A$2:$A$17))=0, NA(), 1))
becomes
IF(SUM({1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0})=0, NA(), 1))
becomes
IF(1=0, NA(), 1))
becomes
IF(False, NA(), 1))
and returns 1
Step 5 - Return time value
=INDEX(Sheet1!$H$2:$H$17, IF(SUM(--(B2=Sheet1!$C$2:$C$17)*(A2=Sheet1!$A$2:$A$17))=0, NA(), MIN(IF((B2=Sheet1!$C$2:$C$17)*(A2=Sheet1!$A$2:$A$17), MATCH(ROW(Sheet1!$A$2:$A$17), ROW(Sheet1!$A$2:$A$17)), ""))))
becomes
=INDEX(Sheet1!$H$2:$H$17, 1)
becomes
=INDEX({0,435416666666667; 0,392361111111111; 0,0277777777777778; 0,216666666666667; 0,109027777777778; 0,0104166666666667; 0,226388888888889; 0,222916666666667; 0,222916666666667; 0,222222222222222; 0,435416666666667; 0,434027777777778; 0,392361111111111; 0,364583333333333; 0,0270833333333333; 0,0104166666666667}, 1)
and returns 0,435416666666667 in cell C2. Formatted as time, 10:27:00
Lookups category
This article demonstrates formulas that extract the nearest number in a cell range to a condition. The image above shows […]
Question: How would I go about looking up data in a cross-reference table. I have the header row (i.e. 24) […]
This article demonstrates a formula that returns the last matching value based on a given condition. The above image shows […]
Excel categories
One Response to “Find entry based on conditions”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
I have a table Columns A:L with the following headings: Departure Airport, Gate, Destination Airport, Gate, Airline, AICO, Call Sign, Flight No., Country, Distance. I have up to 1000 rows.
on a separate tab, I want to create a list based on the departing and arriving airport: the list should contain departing airport, destination airport, which airline and the flight number. Ie.: if i type in my query depart New York and arrive in Montreal, i want the result to show me which airline and flight number can do that flight.
Possible to do?
Marcel