Compare two columns in Excel and insert a new row

0
votes

I am having good (bad) time with Excel vba.

I have two sheets in a workbook- sheet1 and sheet2.

Sheet 1

AccountNo   Account Name    
110101      Imprest        
110102      abs        
110104      abs - Call  
110105      abc-MANAGED 
110109      bda - Dollar    
110201      jhk - Dollar

Sheet 2

AccountNo   Account Name    
110101      Imprest        
110102      abs
110103      bas 
110104      abs - Call  
110105      abc-MANAGED 
110109      bda - Dollar    
110201      jhk - Dollar

I want to compare row the AccountNo column to find out the new accountNo that has been added to sheet2 but not in sheet1. If the AccountNO is found, I want to insert it into sheet1 and it must be inserted into the best position in sheet1 because the account numbers are arranged serially. Example is the 110103 accountNO, i want to insert the entire row after 110102 account no in sheet1.

Dim rngCell As Range
Dim matchRow
For Each rngCell In Worksheets("Sheet2").Range("A2:A200")
    If WorksheetFunction.CountIf(Worksheets("Sheet1").Range("A2:A200"), rngCell) = 0 Then
        ' Range("K" & Rows.Count).End(xlUp).Offset(1) = rngCell
        matchRow = rngCell.Row
        Rows(matchRow & ":" & matchRow).Select
        rngCell.EntireRow.Copy
        Range("K" & Rows.Count).PasteSpecial xlPasteValues
    End If
Next

The code seem not to help. only able to identify the new accountNOs.

2
excelvbaexcel-2010
What's that last line doing? Why don't you just insert the new lines at the bottom of sheet 1 and then sort by a/c no at the end? – SJR
Of if sheet 2 always has more than or the same as sheet 1 just copy the former? – SJR
Sounds like you are making this more difficult than necessary - if sheet2 has more rows than sheet1, copy the who column from sheet 2 and replace column 1 in sheet 1. – caaax
The sheet1 is linked to some other sheets and the position of these values are needed.( i dont know how to express it by typing). sheet1 normaly has some old account no(s) that are no longer needed in sheet2 but are needed in the some other sheets. – Nkoro Joseph Ahamefula

2 Answers

1
votes

If the account numbers are actually in order in both sheets, you don't really need to use CountIf. You can simply iterate the rows of Sheet2 and compare their account number to the account numbers in Sheet1 at the same position. Then you insert a row in that position if you don't find a match in Sheet1.

Try something like this:

Sub Test()
    Dim sourceCell As Range, targetCell As Range
    Dim i As Integer
    For i = 2 To 200
        Set sourceCell = Worksheets("Sheet2").Range("A" & i)
        Set targetCell = Worksheets("Sheet1").Range("A" & i)
        If targetCell.Value <> sourceCell.Value Then
            sourceCell.EntireRow.Copy
            targetCell.EntireRow.Insert
            targetCell.Offset(-1, 0).EntireRow.PasteSpecial xlPasteValues
        End If
    Next
End Sub

Note: This assumes that you do not want to overwrite the existing values in Sheet1, otherwise, you can just copy all the rows of Sheet2 and paste them in Sheet1. For example:

Worksheets("Sheet2").Range("A1:B200").Copy
Worksheets("Sheet1").Range("A1:B200").PasteSpecial xlPasteValues
0
votes

If you want to stay closer to your original code, the method you're trying isn't impossible. All you need is to copy the row from the second sheet, and insert it on the same row you copied it from:

Sub match()
Dim rngCell As Range
Dim matchRow As Integer, nextcol As Integer

For Each rngCell In Worksheets("Sheet2").Range("A2:A200")
    If rngCell <> "" Then
        If WorksheetFunction.CountIf(Worksheets("Sheet1").Range("A2:A200"), rngCell) = 0 Then
            Sheet2.Range(Sheet2.Cells(rngCell.Row, 1), Sheet2.Cells(rngCell.Row, Columns.Count).End(xlToLeft)).Copy
            Sheet1.Range("A" & rngCell.Row).Insert
            End If
    End If
Next
End Sub

Note I have put a column counter in the copy amount, this prevents you from copying the entire row, which might speed up your code if you have many hits.