VBA/Vlookup for Matching two Columns, if unmatched, returning the entire row to another sheet.

[rishi5590]

I want to match two different sheets with respect to serial number given in them in a coloumn(say in A for Sheet1, in E for sheet2); if the serial number is unmatched the entire row with its data ranges should get copied to the 3rd sheet. Searching for a thread for this since last 4 hours. Please help. I need it urgently. I can explain it better if someone needs me to explain. Please!

 

[rishi5590]

....contd. I have this code which is giving me unmatched values in the other sheet, but not the entire row. I need that entire row beside these unmatched numbers. Can some one edit that part for me, it will be a great favor.

Sub test()
    Dim a, e
    With CreateObject("Scripting.<wbr>Dictionary")
        .CompareMode = vbTextCompare
        With Sheets("Sheet1")
            a = .Range("e1", .Range("e" & Rows.Count).End(xlUp)).Value
         End With
        For Each e In a
            If Not IsEmpty(e) And Not .exists(e) Then .Add e, Nothing
        Next
        With Sheets("Sheet2")
            a = .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Value
         End With
        For Each e In a
            If .exists(e) Then .Remove (e)
        Next
        ThisWorkbook.Sheets(4).Range("<wbr>a1").Resize(.Count).Value = Application.Transpose(.keys)
    End With
 End Sub

 

[MrsPen]

Hi Rishi,
I have not tested this code but I believe what you're looking for is EntireRow.Delete. Try this out:


Sub test() Dim a, e With CreateObject("Scripting.<wbr>Dictionary") .CompareMode = vbTextCompare With Sheets("Sheet1") a = .Range("e1", .Range("e" & Rows.Count).End(xlUp)).Value End With For Each e In a If Not IsEmpty(e) And Not .exists(e) Then .Add e, Nothing Next With Sheets("Sheet2") a = .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Value End With For Each e In a If .exists(e) Then .EntireRow.Delete (e) Next ThisWorkbook.Sheets(4).Range("<wbr>a1").Resize(.Count).Value = Application.Transpose(.keys) End With End Sub</pre>
Cheers!

 

[MrsPen]

Sorry, let's try that again:

Sub test()    Dim a, e    With CreateObject("Scripting.<wbr>Dictionary")        .CompareMode = vbTextCompare      
 
 With Sheets("Sheet1")           
 a = .Range("e1", .Range("e" & Rows.Count).End(xlUp)).Value         
End With       
 For Each e In a            
If Not IsEmpty(e) And Not .exists(e) Then 
.Add e, Nothing        

Next        
With Sheets("Sheet2")           
 a = .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Value        
End With       

 For Each e In a           
 If .exists(e) Then [COLOR=#FF0000].EntireRow.Delete[/COLOR] (e)        
Next        

ThisWorkbook.Sheets(4).Range("<wbr>a1").Resize(.Count).Value = Application.Transpose(.keys)    
End With End Sub

 

[rishi5590]

HI, Thanks MrsPen for replying, but its not working. It is giving me an error and when I am debugging it,its is showing '.EntireRow.Delete (e)' as the problem. Any idea?

 

[rishi5590]

Secondly, I am not looking for something to delete the entire row. I actually want that row in a new sheet because I have to submit it as report, these entries are missing in the file. Help!