Thanks:  0
Likes:  0

# Thread: List info that meets 2 criteria

1. 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

[ This Message was edited by: Dave Patton on 2002-04-03 10:09 ]

3. Thanks

I asked this for someone else.

Like many people, he does not use

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

4. 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.

5. 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

[ 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"}

P.S. In order array-enter a formula, one needs to hit control+shift+enter at the same time, not just enter.

Thanks very much.

Suggestions certainly have some very educational aspects.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•