Bill Truax asks:

hello Oscar, i am building a spreadshet for tracking calls for my local fire depatrment. i have column "a" as incident number. the incident number is a one timme 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 scene. i am needing help getting sheet 2 to tag the time spent on a call per apparatus. sheet 2 is names of personnel on scene. i want to put the time on scene according to what apparatus they were on for each incident.

ecample:
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 differant apparatus in column "c".
Column "h" will have on scene time calculated by "=f5-d5"(for that row)
i need to tag the on sceen time from sheet 1 column "h" to the corrisponding 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

Answer:

Here is your data presented in sheet1:

The array formula reads incident numbers and apparatus values therefore I entered missing incident numbers.

How to enter a single formula in all blank cells in a cell range

  1. Select cell range A1:A17
  2. Press F5
  3. Click "Special"
  4. Click "Blanks"
  5. Click "OK"
  6. Type =A3
  7. Press and hold Ctrl
  8. Press Enter

Array formula in sheet2

Array formula in cell C2:

=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)), ""))))

How to enter an array formula

  1. Double click cell C2
  2. Copy/Paste array formula
  3. Press and hold Ctrl and Shift
  4.  Press Enter

How to copy array formula

  1. Select cell C2
  2. Copy (Ctrl + c)
  3. Select cell range C3:C10
  4. 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

Download excel 2007 *.xlsx file

Bill Truax.xlsx