List info that meets 2 criteria
MZ Tools makes life easier for the Excel VBA coder
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: List info that meets 2 criteria

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,334
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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 ]

  2. #2
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Advanced Filter should work

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,334
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    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?

  4. #4
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,861
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    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.




  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,334
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

      


    To Aladin and Lenze

    Thanks very much.

    Suggestions certainly have some very educational aspects.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com