On 2002-04-03 10:07, Dave Patton wrote:
With a table say A1:C500, what is the best way to put the City names in Column F that meet the criteria in D3 and E3
If criteria are Unit T22 and Time 9:00,
the results should be Calgary and London.
A.......B.......C...... D...... E...F
Unit Time City Criteria
T22 9:00 Calgary Unit Time
T22 9:00 London
Tc 10:15 Washington
T22 12:00 Paris
Thanks for your help.
This message was edited by Dave Patton on 2002-04-03 10:09
Hi Dave,
It seems a formula-based solution is required.
I'll assume the sample you provided, extended with one more record:
{"Unit","Time","City","Criteria","";
"","","","","";
"T22",0.375,"Calgary","T22",0.375;
"T22",0.375,"London","","";
"Tc",0.427083333333333,"Washington","","";
"T22",0.5,"Paris","","";
"T22",0.375,"Istanbul","",""}
The sample is in A1:E7. Empty cells are represented by "" in the above array. The criteria are in D3:E3 as specified.
In F1 enter: =ROW(2:2)
which computes the number of rows before the first row of actual data.
In F2 enter:
=MATCH(9.99999999999999E+307,B:B)
In F3 enter:
=IF(LEN(G3),MATCH(G3,OFFSET(C$3,0,0,F$2,1),0),"")
and copy this down to a few rows.
In G3 array-enter:
=INDEX(C3:INDIRECT("C"&F$2),MATCH(D3&"#"&E3,A3:INDIRECT("A"&F$2)&"#"&B3:INDIRECT("B"&F$2),0))
In G4 array-enter:
=IF(SUM((OFFSET(A$3,0,0,F$2,1)&"#"&OFFSET(B$3,0,0,F$2,1)=D3&"#"&E3)+0)>=COUNTA(G$3:G3),INDEX(C$3:INDIRECT("C"&F$2),MATCH(D$3&"#"&E$3,INDIRECT("A"&F$1+F3+1):INDIRECT("A"&F$2)&"#"&INDIRECT("B"&F$1+F3+1):INDIRECT("B"&F$2),0)+F3),"")
and copy this down to a few rows.
A data area of 500 rows is not too big, but it would be better create a hidden column between the current B and C and apply in the new column the formula:
=A3&"#"&B3 [ for all of the data in A and B ]
This move can be used to change costly array-formulas into ordinary formulas.
The processing/results area will look like this:
{2,"";
7,"";
1,"Calgary";
2,"London";
5,"Istanbul"}
Aladin
P.S. In order array-enter a formula, one needs to hit control+shift+enter at the same time, not just enter.