List info that meets 2 criteria

[Dave Patton]

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

 

[lenze]

Advanced Filter should work

 

[Dave Patton]

Thanks

I asked this for someone else.

Like many people, he does not use
Advanced Filter or Database functions.

Is there a formula that will refresh automatically when he changes the criteria?

 

[lenze]

Perhaps he could create a pivot table on his sheet. Put Unit and time in the page field, City in the Row field, and City in the Data Field.

 

[Aladin Akyurek]

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.

 

[Dave Patton]

To Aladin and Lenze

Thanks very much.

Suggestions certainly have some very educational aspects.