Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Filtering Criteria

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have tried asking this a couple of times with no viable results, which means I am not wording it correctly. So, I will try again. I have an inventory list up to 10000 items long (the rows) with several several entrys for each item (the columns). each item has a unique designator to identify it. the designator is an alpha numeric string such as MM1 or BB7A. An operator will type in the identifier looking for a single item. Key word here is single. A Macro will take her typein and filter the list for the item and return it to her. My problem is in the critera for the filter, the item number. If An item number of, say BB6 is used as criteria, all items that contain BB6 in them, such as BB61 or BB67 will be returned. I need only the one unique item, BB6 to be returned. I know I can do this by using a criteria of '=BB6 or ="=BB6" but I cant figure out how to get the added charaters onto the BB6 string under macro control? I need to do this because then, if more than one item is returned, it must be a duplicate or if no items are returned, it must be a bad item number.

    Can anyone help me, I have tried for many hours to figgure this out myself, but I guess I am just plain stupid. AAAARRRRRGGGGHHHH

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Sheffield, UK
    Posts
    253
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I can see why you are having a difficult time describing what you want - struggling to get my head around what you mean. Is there any way you could send me a bit of sample data? I think I know how to do what you want, but I may be misunderstanding the complexity of it.

  3. #3
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I used a function like this yesterday:

    =IF(ISERROR(FIND(UPPER("deposit"),UPPER(C2))),"",H2)

    The function finds the word "deposit" ANYWHERE in the string. I think our asker needs to find ONLY THOSE RECORDS where the string is "deposit" and nothing else.

    _________________
    TheWordExpert

    [ This Message was edited by: Dreamboat on 2002-05-09 05:02 ]

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The problem in this is the number that is in the string. Excel cant seem to just look for a single number. The list would look something like this within the criteria column:

    MM1
    MM2
    MM3A
    MM3B
    MM4
    .
    .
    .
    .
    MM21A
    MM21B
    MM22
    MM23
    O1A
    O1B
    O2

    And so on.

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Sheffield, UK
    Posts
    253
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    is the reference code in the middle of a string? What I mean is, does the cell with the reference number look like this:

    'MM16 : Item description blah blah blah

    or does it just contain the reference itself?

    [ This Message was edited by: philR on 2002-05-09 05:44 ]

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

    Default


    On 2002-05-09 05:01, Dreamboat wrote:
    I used a function like this yesterday:

    =IF(ISERROR(FIND(UPPER("deposit"),UPPER(C2))),"",H2)

    The function finds the word "deposit" ANYWHERE in the string.


    =IF(ISNUMBER(SEARCH("deposit",C2)),H2,"")

    would have been sufficient.

    And, there is no need for an ISERROR test. Functions like FIND, SEARCH, and MATCH, they all return a number when successfull. ISNUMBER is the appropriate function/predicate to check whether the returned result is indeed a number.


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

    Default

    On 2002-05-09 04:39, elgringo56 wrote:
    I have tried asking this a couple of times with no viable results, which means I am not wording it correctly. So, I will try again. I have an inventory list up to 10000 items long (the rows) with several several entrys for each item (the columns). each item has a unique designator to identify it. the designator is an alpha numeric string such as MM1 or BB7A. An operator will type in the identifier looking for a single item. Key word here is single. A Macro will take her typein and filter the list for the item and return it to her. My problem is in the critera for the filter, the item number. If An item number of, say BB6 is used as criteria, all items that contain BB6 in them, such as BB61 or BB67 will be returned. I need only the one unique item, BB6 to be returned. I know I can do this by using a criteria of '=BB6 or ="=BB6" but I cant figure out how to get the added charaters onto the BB6 string under macro control? I need to do this because then, if more than one item is returned, it must be a duplicate or if no items are returned, it must be a bad item number.

    Can anyone help me, I have tried for many hours to figgure this out myself, but I guess I am just plain stupid. AAAARRRRRGGGGHHHH
    Which problem are you wording: the filtering problem or the macro problem or both? I thought your filtering was resolved in another thread.

  8. #8
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


  9. #9
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    My problem is that I cant filter for something like for a cell that contains something like MM1 and find it singulary. any cell in the column that I am looking at that contains MM1, such as a cell that contains MM16 will also be found. This is driving me nuts

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

    Default

    On 2002-05-09 06:17, elgringo56 wrote:
    My problem is that I cant filter for something like for a cell that contains something like MM1 and find it singulary. any cell in the column that I am looking at that contains MM1, such as a cell that contains MM16 will also be found. This is driving me nuts
    Use my computed criteria proposal in the other thread. That will do exactly what you want. The question of how to get that under macro control, if you need help for that too, must have to be done by someone like TsTom.

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
  •