Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Advanced Filter (Excel 2000)

    I have a large 12,000 row data base. Above that I have an Advanced Filter where the criteria for column D is 133 rows - the filtered list is 3,000 rows. If there is a match on the criteria, then I want the word "Other" to appear in Column C. How do I accomplish this? Thank you.

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

    Re: Advanced Filter (Excel 2000)

    Do you mean that you want "Other" in column C in the 3,000 filtered rows?

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advanced Filter (Excel 2000)

    Yes.

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

    Re: Advanced Filter (Excel 2000)

    Say that the values for the criteria are in D2134 (D1 is the field name) and that the data table is in rows 201-12200 (row 200 contains the field names). Put the following formula in C201:
    <code>
    =IF(ISNA(MATCH(D201,$D$2:$D$134,0)),"","Other")
    </code>
    and fill down to row 12200. (Of course, you must adjust the coordinates to the actual values for your spreadsheet).

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advanced Filter (Excel 2000)

    This did not work. It placed "Other" in all 12000 rows, even those that did not have a match with the exact text. This was my formula in G137:
    =IF(ISNA(MATCH($H$2:$H$133,0)),"","Other"). Column H is a text column. Thank you.

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

    Re: Advanced Filter (Excel 2000)

    You have omitted the search value from the MATCH function, i.e. the cell in row 137 to be compared to H2:H133. I don't know which cell, since the situation apparently is different from what you originally described.

  7. #7
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advanced Filter (Excel 2000)

    Got it! Thank you.l

Posting Permissions

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