Filtering Criteria

[elgringo56]

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

[philR]

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.

[Anne Troy]

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 ]

[elgringo56]

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.

[philR]

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 ]

[Aladin Akyurek]

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.

[Aladin Akyurek]

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.

[elgringo56]

[elgringo56]

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

[Aladin Akyurek]

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.