SEARCH WORKSHEET USING A NOT CRITERIA

[lghovden06]

I'm updating a new price sheet for a business from the new prices online. On
the new sheet from the internet there are bulk, 55 GA drums, 12/1 packages
and so on. I only need the prices for everything but the bulk. Each product
is listed like this
Ursa 15/40
Ursa 15/40 (55GA NRD)
Ursa 15/40 (12/1-GT)
Ursa 15/40 (5Ga Pal)
Rando 68....
So the Ursa 15/40 and Rando 68 I don't need. There are about 4,000
products, so to go through and delete them one at a time is time consuming.
I tried to search, but I can't search for NOT something ex :
Find: NOT: "*(55GA NRD)" I believe the * will search for any string of
characters before (55GA NRD).
Then I tried to insert a new column and do a conditional formula.
ex: =NOT(E5= "*(55GA NRD)")
However this did not work either. Can any one help solve my problem.
The example data is housed in one column and the prices in another and
the bulk items will always be shorter in that product. I do not believe
the words before are unique. (i.e. there is Rando 32, Rando 46, and Rando
68. there is also Ursa 5/30, Ursa 10/30, Ursa 15/40 and so on)

PLEASE HELP
THANK YOU

 

[Pete_UK]

One way of doing this relies on the data being in the order you give
in your example, i.e. Ursa 15/40 (refering to bulk) has fewer
characters than the following items, so you could introduce a formula
in a helper column which identifies these as needing to be deleted.
You can then apply autofilter to that column and delete all the
offending articles in one operation. So, assuming your first data item
is in A1, put this formula in C1:

=IF(AND(LEN(A1)<LEN(A2),ISNUMBER(SEARCH(A1,A2))),"Delete","")

Then copy this down for as many items as you have, and you should find
that the first entry in any block is marked for deletion (confirm by
scrolling down). Then apply autofilter to the helper column, selecting
"Delete" from the filter pull-down. Then highlight all the visible
rows and Edit | Delete Row. You should now be left with the data you
need and you can delete the helper column.

Hope this helps.

Pete

 

[lghovden06]

IT ALMOST WORKED!
I put this formula in a seperate column
=IF(AND(LEN(E1623)<LEN(E1624),ISNUMBER(SEARCH(E1623,E1624))),"Delete","")

For the most part it worked. about 30 rows didn't work, which isn't a big
deal, but if it could be perfected it would be nice. some of the products
that didn't work for example are

CHV SYN COMP OIL FM ISO 46 (5G P PL)
CHV AQUAGEAR EP 220
CHV AQUAGEAR EP 320 (400#NRDL)
CHV AQUAGEAR EP 460
CHV DELO SYNGEARLUBE 75W90(120#NRQD)

CHV URSA SUPER PLUS SAE40 (55GA NRD)
CHV URSA SUPER PLUS SAE50
CHV URSA SUPERPLUS EC15W40 (55GA NRD)

i don't know if this can be fixed or not

Thank your for your help

 

[Pete_UK]

Well, I think that's about as near as you can get without spending a
lot of time compiling a complete list of what might follow the base
name and using that to identify non-bulk items.

I can see why these would not be identified:

CHV AQUAGEAR EP 220
CHV AQUAGEAR EP 460
CHV URSA SUPER PLUS SAE50

Although they look like bulk items you do not have non-bulk items of
the same product in the rows that follow, so the first one EP 220 does
not match with the next item EP 320.

However, in the examples you have given so far the items which are not
bulk seem to have brackets in the description. If this is always the
case then you might like to try something like this in the helper
column:

=IF(ISNUMBER(SEARCH("(",E1623)),"","Delete")

and copy down, to see if this has a better success rate - it marks for
deletion all those items which don't contain an open bracket.

Hope this helps.

Pete

 

[lghovden06]

IT WORKED
the new formula got rid of every bulk product. and i also used it to find
all of the 55GA drums, 12/1-QT, and so on
thank you

 

[Pete_UK]

You're welcome - glad we got there in the end, and you seem to have
taken the principle further as well.

Pete