need formula for finding and removing orders

[Kenekio]

Can any one help with what Im trying to do. I have a list of all of our Open line Items. I need a formula or VBA that can locate all orders that have Part #'s 2000 & 2005 and remove the entire order including the other part #'s on the same order. See example below.
Noted the lines that need to be removed because there order contains a 2000 or 2005.

A B C
1 Order # Part # Qty
2 137047 1951 2
3 137050 1952 4
4 137086 2000 1 Remove
5 137086 1951 1 Remove
6 137095 2000 2 Remove
7 137096 2000 4 Remove
8 137096 1955 1 Remove
9 137100 1957 1

[Cutter]

Couldn't you just sort on the Part # and then delete those 2000 entries?

I don't see why you have "REMOVE" adjacent to items 5 or 8 as they are not 2000 or 2005.

[Kenekio]

Couldn't you just sort on the Part # and then delete those 2000 entries?

I don't see why you have "REMOVE" adjacent to items 5 or 8 as they are not 2000 or 2005.

I have to remove the entire order from this list and if I sort and delete the 2000 and 2005's I get the other items on those orders still on there. This is basically a flat file of all open orders. The entire Order containing 2000 & 2005 are shipped from another location. This includes numbers like1951,1952 that we have at both locations. I need to remove the whole order.

[Cutter]

Without seeing sample file I may not be visualizing correctly but:

Step 1
Sort everything using Part #

Step 2
Delete Part # and Quantity data only for 2000 & 2005. Leave Order #'s intact

Step 3
Resort everything using Order #

Step 4
Delete Order #'s that have a blank anywhere in their range

[Kenekio]

This will not work beacause I dont want any record of that entie order. Example: Order # 137086 is an order for 2 Part #'s Part # 2000 and Part # 1951. I am looking for something that can remove that entire Order from the list. If I sort and remove all rows with 2000, the rest of the order is still on the list. 137086 should be off the list completely.

[Cutter]

I think you need to read my last post again.

I didn't suggest removing "all rows with 2000"

If you actually try what I suggested you might see what I mean.

[Kenekio]

How would I delete the order #'s with blank in there range? Sorry if im not getting it... also thank you very much for your replies

[JBeaucaire]

I get it. If a part # is 2000 or 2005, look at the order # and delete all the rows with that order #.

[Kenekio]

I get it. If a part # is 2000 or 2005, look at the order # and delete all the rows with that order #.

Exactly. Can u help?

[JBeaucaire]

Here's a macro that will do it.

Please Login or Register  to view this content.

[Kenekio]

You are Awesome. Work Fantastic. If I wanted to add new numbers that it would look up and remove orders for as well what would i have to add/change in the vba code. Im soooo stoked that you made this work so well.

[JBeaucaire]

Would you like a popup to ask?

[Kenekio]

Generally its always going to be the same #'s so I dont think it needs to ask but that would be a great feature. The actual file im using is much larger with many more columns and rows. I thought I could easily change the VBA but im running into an issue when I do so. What Would I have to do if the order numer was in Column "C" and Part# is in Column "AL". Also if you could tell me how easy it is to add more criteria to search for that would be cool. Im still in Awe that you created this in minutes and it works so well on my test sheet.

[JBeaucaire]

Ok, this will ask you for the part numbers to delete, and it will find the ORDER # column and the PART # column wherever it is in row 1. Make sure the spelling is correct and matching the sample sheet you uploaded.

Please Login or Register  to view this content.

[Kenekio]

Maybe I dont get the row 1 part. Can I use this on a large spreadsheet where Order is in Column C and Part # is in column AL? Is this possible. Everything worked Great on Sample sheet with the 3 columns. Not sure Where to go with this large sheet tho

[Kenekio]

im sorry nevermind. I misread and I had my headers mis titled. That is great.

[Kenekio]

Amazing!!!! You are Amazing. Thanks for all your help. Your a lifesaver!!!

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

[JBeaucaire]

If you have an updated need, post an updated workbook demonstrating the new problem.