I have 2 worksheets named sheet1 and need_to_delete that are in the same format:
7 digit number, 5 digit alpha numeric, text, dollar amount.
I need to copy every row into sheet2 where there are no matches in the column A of the two source worksheets, and copy every row that does have a match in column A into sheet 3.
I also need to keep the rows in their current order.
Re: Copy Row To One Of Two Worksheets Based On Match Condition
Here is a clunky way to do it using a macro.
Sub Compare()
Dim i As Long
Sheets("need_to_delete").Select
Columns("A:A").Copy
Sheets("Sheet2").Select
Range("A1").PasteSpecial xlPasteAll
Sheets("Sheet3").Select
Range("A1").PasteSpecial xlPasteAll
Sheets("Sheet1").Select
Columns("A:A").Copy
Sheets("Sheet2").Select
Range("C1").PasteSpecial xlPasteAll
Sheets("Sheet3").Select
Range("C1").PasteSpecial xlPasteAll
'NonMatches
Sheets("Sheet2").Select
Range("B1").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-1],C[1],1,FALSE)),0,1)"
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B" & Range("A65536").End(xlUp).Row)
Range("D1").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-1],C[-3],1,FALSE)),0,1)"
Range("D1").Select
Selection.AutoFill Destination:=Range("D1:D" & Range("C65536").End(xlUp).Row)
Cells.Copy
Range("A1").PasteSpecial xlPasteValues
For i = Range("A65536").End(xlUp).Row To 1 Step -1
If Range("B" & i).Value = 1 Then Range("A" & i & ":B" & i).Delete xlUp
Next i
For i = Range("C65536").End(xlUp).Row To 1 Step -1
If Range("D" & i).Value = 1 Then Range("C" & i & ":D" & i).Delete xlUp
Next i
Range("C1:C" & Range("C65536").End(xlUp).Row).Copy
Range("A" & Range("A65536").End(xlUp).Row + 1).PasteSpecial xlPasteAll
Columns("B:D").Delete
'Matches
Sheets("Sheet3").Select
Range("B1").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-1],C[1],1,FALSE)),0,1)"
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B" & Range("A65536").End(xlUp).Row)
Columns("B:B").Copy
Range("B1").PasteSpecial xlPasteValues
For i = Range("A65536").End(xlUp).Row To 1 Step -1
If Range("B" & i).Value = 0 Then Range("B" & i).EntireRow.Delete xlUp
Next i
Columns("B:C").Delete
End SubHTH
Sorry, I forgot you had 4 columns of data.
Sub Compare()
Dim i As Long
Sheets("need_to_delete").Select
Columns("A:D").Copy
Sheets("Sheet2").Select
Range("A1").PasteSpecial xlPasteAll
Sheets("Sheet3").Select
Range("A1").PasteSpecial xlPasteAll
Sheets("Sheet1").Select
Columns("A:D").Copy
Sheets("Sheet2").Select
Range("F1").PasteSpecial xlPasteAll
Sheets("Sheet3").Select
Range("F1").PasteSpecial xlPasteAll
'NonMatches
Sheets("Sheet2").Select
Range("E1").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-4],C[1],1,FALSE)),0,1)"
Range("E1").Select
Selection.AutoFill Destination:=Range("E1:E" & Range("A65536").End(xlUp).Row)
Range("J1").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-4],C[-9],1,FALSE)),0,1)"
Range("J1").Select
Selection.AutoFill Destination:=Range("J1:J" & Range("G65536").End(xlUp).Row)
Cells.Copy
Range("A1").PasteSpecial xlPasteValues
For i = Range("A65536").End(xlUp).Row To 1 Step -1
If Range("E" & i).Value = 1 Then Range("A" & i & ":E" & i).Delete xlUp
Next i
For i = Range("F65536").End(xlUp).Row To 1 Step -1
If Range("J" & i).Value = 1 Then Range("F" & i & ":J" & i).Delete xlUp
Next i
Range("F1:I" & Range("F65536").End(xlUp).Row).Copy
Range("A" & Range("A65536").End(xlUp).Row + 1).PasteSpecial xlPasteAll
Columns("E:J").Delete
'Matches
Sheets("Sheet3").Select
Range("E1").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-4],C[1],1,FALSE)),0,1)"
Range("E1").Select
Selection.AutoFill Destination:=Range("E1:E" & Range("A65536").End(xlUp).Row)
Columns("E:E").Copy
Range("E1").PasteSpecial xlPasteValues
For i = Range("A65536").End(xlUp).Row To 1 Step -1
If Range("E" & i).Value = 0 Then Range("E" & i).EntireRow.Delete xlUp
Next i
Columns("E:I").Delete
End SubRe: Copy Row To One Of Two Worksheets Based On Match Condition
Brian, this looks like it works except for one small problem. It is only copying the column I am comparing. I need to get the whole row. How could I modify this to copy the entire row?
Also, I seem to have 1 too many entries in the "no match" worksheet, but I think this just may be an issue with how the data was formatted when it was given to me. I had to do some cleanup, and I may have missed something. I am double checking my data too see if it is my fault like I am guessing.
Re: Copy Row To One Of Two Worksheets Based On Match Condition
I don't know if you saw that I edited my post to show how to get all 4 columns of data (the second VB code section). It is a little easier if we can predefine the columns chosen, but if you need that to be variable, let me know.
Re: Copy Row To One Of Two Worksheets Based On Match Condition
Thank you Brian, I ran that, and to me it looks like it did the job. I am going to turn it over to the user to make sure its what she needs.
Re: Copy Rows To Worksheet If Cross Match & Another If No Match
AdvancedFilter
Don’t have an account yet? Register yourself now and be a part of our community!