Using Macro to Find Remaining Data in the Table after Vlookup Used

[madhuchelliah]

Hello Folks, I have 3 sheets in my WB, in the sheet2 i have the source data in table format from C column to H column. The first cell of all the columns has heading. In my sheet1 i used vlookup to pull data from the table in sheet2. My lookup value is in C column of sheet2. My requirement is after using the vlookup i want to extract the data of remaining lookup value data which is not picked by vlookup to the sheet3 in table format. My Extracted data using vlookup is in sheet1 from D10 cell. Let me explain the case with simple example. In sheet 2 the table contains 10 students name with their marks in respective subjects. If i pull date of 5 students data to sheet1 using vlookup there are remaining 5 students. I want that remaining 5 students data in sheet3. Please help. Thank you

 

[frank_AL]

Can you copy and paste from your worksheet what it looks like after you run VLOOKUP? You can change names as needed so as not to show any personal information.

 

[madhuchelliah]

Hello Frank,In sheet2 i have my source which is details of all students. Using vlookup i retrieve some students details to Sheet1. In Sheet3 i want the remaining students name which is not in the sheet1. Sheet3 is the result i needed. Thank you. Check the link below for the excel sheet. It is for reference only.

https://www.dropbox.com/s/j838echjjmbcdks/Test Copy.xlsx?dl=0

 

[frank_AL]

Give this code a try. Appears to be working properly based on the example file you provided. Let me know if you encounter and problems.

Option Explicit


Sub FindRemData()


Dim wstable As Worksheet
Dim wscopyto As Worksheet
Dim wstest As Worksheet
Dim lastrow1 As Long
Dim lastrow2 As Long
Dim nextrow As Long
Dim nametest As Range
Dim i As Long


Set wstest = Worksheets("Sheet1")
Set wstable = Worksheets("Sheet2")
Set wscopyto = Worksheets("Sheet3")


lastrow1 = wstest.Cells(wstest.Rows.Count, "D").End(xlUp).Row
lastrow2 = wstable.Cells(wstable.Rows.Count, "C").End(xlUp).Row
nextrow = wscopyto.Cells(wscopyto.Rows.Count, "C").End(xlUp).Row + 1


For i = 2 To lastrow2
    Set nametest = wstable.Cells(i, 3)
    If Not Application.WorksheetFunction.CountIf(wstest.Range("D10:D" & lastrow1), nametest) > 0 Then
        Sheet2.Range("C" & i & ":I" & i).Copy _
        Destination:=Sheet3.Range("C" & nextrow & ":I" & nextrow)
        nextrow = nextrow + 1
    End If
Next i


End Sub