Filtering Criteria

elgringo56

Well-known Member
Joined
Apr 15, 2002
Messages
869
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top