To Filter move out the Invalid data to a new sheet.

aramdc

New Member
Joined
Jan 29, 2010
Messages
7
i have two sheets.
Sheet 1 list contains orders & part nos.
While the other contains the part no which are in stock.
the format is as shown below.

Sheet 1
Order no Part
1001 x
1002 y
1003 z
1004 x
1005 a
Sheet 2
Part no Stock
x 1
y 1

Sheet 3 should give me the following
Order no part
1003 z
1005 a


I used data validation to circle out the parts which i don't have..
I want this new parts & their order to appear in a separate list with the order number so i could decide if i really want to keep that part in stock or not.
here i am dealing with more than 1000 parts to checking it becomes difficult.
So i want to separate out the parts which i dont have .

how can i do this with data validation or advance filtering.
if not possible with functions wat would be the VBA code to do it?
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
try using the data - filter - auto filter
in column B with the drop down choose custom drop down "does not contain" X in the first one then "does not contain" Y in the second one with select the "AND"

then you can select the rows shown to move to another sheet.
 
Upvote 0
This code will copy rows from Sheet1 to Sheet3 that part numbers do not exist in Column A of Sheet2.
Code:
Sub CopyParts()
Dim Rng1 As Range, Rng2 As Range, i As Long

    Set Rng1 = Range("B2", Range("B65536").End(xlUp))
    Set Rng2 = Sheets("Sheet2").Range("A2", Sheets("Sheet2").Range("A65536").End(xlUp))
    
    For i = 2 To Rng1.Rows.Count + 1
        If Application.WorksheetFunction.CountIf(Rng2, Cells(i, "B")) = 0 Then
          LR = Sheets("Sheet3").Cells(Rows.Count, "B").End(xlUp).Row + 1
          Rows(i).EntireRow.Copy Destination:=Sheets("Sheet3").Range("A" & LR)
        End If
    Next i

End Sub
Code assumes Sheet1 is the active sheet.
 
Upvote 0
thanks a lot...
i have done it using a combination of Vlookup & advance filtering the values which are NA.
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

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