Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combining MATCH with Autofilter (2003 SP2)

    Hello

    Trying to combine features of the Match() function with Autofilter, but having a brain-freeze. Here's what I am trying to accomplish.

    The spreadsheet contains a database from multiple sample points. I am using AutoFilter to display the data for each sample point, one at a time. Say the database has 300 rows, each sample point may have between 5 and 15 rows. One column contains a formula that designates whether the row is a candidate for additional calculations. It looks something like this (additional columns supressed):

    Filter HD SD
    0 0.00 0.00
    1 -2.91 0.73
    1 -1.48 -0.22
    1 -1.00 0.00
    0 0.00 0.00
    0 0.00 0.00
    0 0.00 0.00

    The "1" in the Filter column indicates the row should be processed further while the "0" indicates it should not be included in subsequent calculations. The tricky part is that the FIRST and the LAST occurrence of the "1" are vital for the additional calculations. There's the problem: what formula can I put into some other cells that will return the value of the FIRST occurrence of HD, the FIRST occurrence of SD, the LAST occurrence of HD, and the LAST occurrence of SD for the rows that are made visible by AutoFilter. They are in variable positions depending on the value of the AutoFilter.

    I tried with a MATCH() function, and it works OK if the formula is hard-wired to the exact rows that are currently made visible by the AutoFilter e.g. =MATCH(1,N38:N44) returns "4", which is the relative rownumber of the last occurrence of the "1". But when I generalize the formula to include ALL the rows of the database, the same formula returns "44". It also displays "44" regardless of which rows have been made visible by AutoFilter.

    If it helps, there is a cell on the spreadsheet that contains the value that is the current AutoFilter.

    Any ideas how I can solve this problem? TIA.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combining MATCH with Autofilter (2003 SP2)

    Could you attach a small sample workbook, including the filter?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining MATCH with Autofilter (2003 SP2)

    Here's the whole thing (under development)

    The first column (WayPt) is the AutoFilter column. The stuff I am trying to extract is in the Prism Coordinates section. Notice that it's repeated for Left and Right -- whatever logic is used to find the first and last of the left section will also be used to do the same on the Right section.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Combining MATCH with Autofilter (2003 SP2)

    Not sure exactly what you want, but if you put in N1 the array formula (confirm with ctrl-shift-enter):
    =MIN(IF((A9:A500=$A$3)*(N9:N500=1),ROW(N9:N500)))
    you will get the minimum of the row that has 1 in col N and the value of A3 in col A

    In N2 enter the array formula (confirm with ctrl-shift-enter):
    =Max(IF((A9:A500=$A$3)*(N9:N500=1),ROW(N9:N500)))
    you will get the maximum of the row that has 1 in col N and the value of A3 in col A

    In O1 enter:
    =INDEX(O$1:O$500,$N1)

    Copy O1 to O1:P2 and you get the the 4 start and end values for cols O and P...

    Steve

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining MATCH with Autofilter (2003 SP2)

    Steve

    That's exactly what I want. I know that my spreadsheet was a bit obscure, but it's only partially developed. You provided a key element that will let me proceed further. Thanks very much.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

Posting Permissions

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