Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Simple (I hope) AutoFilter question

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    101
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm working with a list where I want to filter the items that begin with "* "(a star then a space) and then have a string of letters and numbers after it (it is a model number).

    I turn on the auto filter and I select "custom" for that column. I select "begins with" in the first drop down box and then type * in the second box and type. As I'm sure you know, that is the wild card indicator and I fail to filter out the other data. (I've also typed "*" and "* " to no avail).

    Then I figured I try to do a find/replace on the * (this way, I thought, I could then just sort by what ever I replaced the * with) but I run into the same wildcard issue.

    Do any of you have any good ideas on how to get around this little issue?

    Thanks,
    Steve

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try adding another column to your data set with the folowing formula:
    =if(left(a1,2)="* ","Y","N") assuming your part number is in column A.
    Then you can filter on that column for Y


    _________________
    It's never too late to learn something new.

    Ricky

    [ This Message was edited by: Ricky Morris on 2002-04-22 12:36 ]

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,993
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-22 12:15, SteveD wrote:
    I'm working with a list where I want to filter the items that begin with "* "(a star then a space) and then have a string of letters and numbers after it (it is a model number).

    I turn on the auto filter and I select "custom" for that column. I select "begins with" in the first drop down box and then type * in the second box and type. As I'm sure you know, that is the wild card indicator and I fail to filter out the other data. (I've also typed "*" and "* " to no avail).

    Then I figured I try to do a find/replace on the * (this way, I thought, I could then just sort by what ever I replaced the * with) but I run into the same wildcard issue.

    Do any of you have any good ideas on how to get around this little issue?

    Thanks,
    Steve
    You can use Advanced Filter for this.

    Lets say that A4:A7 houses the following sample:

    {"model";
    "* trax";
    "zxa";
    "* daron"}

    Format the label distinctly, say, as bold and italic.

    In A1 enter: model
    In A2 enter: "* " [ without quotes ]

    Activate A5.
    Activate Data|Filter|Advanced Filter.
    Check Copy to another location.
    Enter as List range:

    $A$4:$A$7

    Enter as Criteria range:

    $A$1:$A$2

    Enter for Copy to e.g,

    $E$1

    Click OK.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    101
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank you!

    Aladin, yours stil showed me selecting everything (I think that Excel thought I was usind the criteria row to say select all).

    Rick, That will work nicely. I'll add a macro to add a row with this formula, filter the sheet, then delete that row. Perfect.

    Thanks again for the help,
    SteveD

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,993
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-22 12:50, SteveD wrote:
    Thank you!

    Aladin, yours stil showed me selecting everything (I think that Excel thought I was usind the criteria row to say select all).

    Rick, That will work nicely. I'll add a macro to add a row with this formula, filter the sheet, then delete that row. Perfect.

    Thanks again for the help,
    SteveD
    This is what I get in E1 and down from the sample I posted:

    {"model";
    "* trax";
    "* daron"}

    Aladin

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-22 12:15, SteveD wrote:
    I'm working with a list where I want to filter the items that begin with "* "(a star then a space) and then have a string of letters and numbers after it (it is a model number).

    I turn on the auto filter and I select "custom" for that column. I select "begins with" in the first drop down box and then type * in the second box and type. As I'm sure you know, that is the wild card indicator and I fail to filter out the other data. (I've also typed "*" and "* " to no avail).

    Then I figured I try to do a find/replace on the * (this way, I thought, I could then just sort by what ever I replaced the * with) but I run into the same wildcard issue.

    Do any of you have any good ideas on how to get around this little issue?

    Thanks,
    Steve
    Simply apply the custom filter...

    Begins with [~* ]

    Note: The square brackets shown above are used to delimit the proper pattern. Don't include them in your match pattern.

    [ This Message was edited by: Mark W. on 2002-04-23 07:25 ]

Some videos you may like

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
  •