The Seemingly Impossible
Max wrote...
Here's one play using non-array formulas ..
Why? This is easier using array formulas.
Assume source table in sheet: X
cols A to G, headers in A1:G1, data from row2 down
col F = Function, col G = Area, col D = Minutes 1
In a new sheet,
Paste the same headers in A1:G1
So far, so good.
Let's reserve I1:I2 for input of the Function & Area
....
Why? Why not just use the F2 and G2 cells?
Put in H2: =IF(AND(X!F2=$I$1,X!G2=$I$2),X!D2-ROW()/10^10,"")
....
Not necessary to use col H for ancillary calculations.
If the incident numbers in X!A:A are distinct,
A2:
=LOOKUP(2,1/(X!$D$2:$D$101-ROW(X!$D$2:$D$101)/2^20
=LARGE((X!$F$2:$F$101=$F2)*(X!$G$2:$G$101=$G2)
*(X!$D$2:$D$101-ROW(X!$D$2:$D$101)/2^20),ROWS(A$2:A2))),X!A$2:A$101)
B2:
=VLOOKUP($A2,X!$A$2:$E$101,COLUMNS($A2:B2),0)
Fill B2 right into C2:E2. F2 would hold the entry for Function and G2
the entry for Area. Fill A2:E2 down into A3:E3. Enter the formulas
F3:
=F$2
Fill F2 right into G3. Fill A3:G3 down into A4:G6. None of these are
array formulas.
This doesn't handle the possibility that there could be fewer than 5
incidents for a particular function and area. If that's a possibility,
then the col A formulas would need to be changed to
A2:
=IF(SUMPRODUCT((X!$F$2:$F$101=$F2)*(X!$G$2:$G$101= $G2))=ROWS(A$2:A2),
LOOKUP(2,1/(X!$D$2:$D$101-ROW(X!$D$2:$D$101)/2^20
=LARGE((X!$F$2:$F$101=$F2)*(X!$G$2:$G$101=$G2)
*(X!$D$2:$D$101-ROW(X!$D$2:$D$101)/2^20),ROWS(A$2:A2))),X!A$2:A$101),"")
And the cols B-E formulas to
B2:
=IF(N($A2),VLOOKUP($A2,X!$A$2:$E$101,COLUMNS($A2:B 2),0),"")
And the cols F-G formulas to
F2:
=IF(N($A2),F$2,"")
|