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

[aramdc]

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?

 

[texasalynn]

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.

 

[Datsmart]

This code will copy rows from Sheet1 to Sheet3 that part numbers do not exist in Column A of Sheet2.

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.

 

[aramdc]

thanks a lot...
i have done it using a combination of Vlookup & advance filtering the values which are NA.