Search code examples
excelexcel-formuladuplicatesexcel-2007

Avoid duplicates in Excel 2007

I need to pull data from Sheet 1 Column A to Sheet 2 Column A. Sheet 2 already has some values in Column A. So,I need to have a formula to pull data from Sheet 1 Column A to Sheet 2. If value already exist, don't need to copy, if not need to pull data.

Eg:
Sheet 2 I have written a formula like: if ID already there in Sheet 1, then will add the marks to the existing one.
img1

Sheet 1 Need to write a formula like: if ID is new to Sheet 2 will add the ID to the next row with the marks.
img2

Will add IDs and marks to Sheet 1 bi-weekly. So, I need Sheet 2 to be a consolidated one. Thanks in advance for the help.

Solution

  • Try

    Option Explicit

Public Sub CopyUniqueOnly()

    Dim currCell As Range, dict As Object
    Set dict = CreateObject("Scripting.Dictionary")

    With ThisWorkbook.Worksheets("Sheet2")
        For Each currCell In .Range("A1", .Cells(.Rows.count, 1).End(xlUp))
            If Not dict.exists(currCell.Value) And Not IsEmpty(currCell) Then
                dict.Add currCell.Value, currCell.Value
            End If
        Next currCell
    End With

    Dim unionRng As Range

    With ThisWorkbook.Worksheets("Sheet1")
        Dim rng As Range
        For Each rng In .Range("A1", .Cells(.Rows.count, 1).End(xlUp))
            If Not dict.exists(rng.Value) And Not IsEmpty(rng) Then
                If Not unionRng Is Nothing Then
                    Set unionRng = Union(rng, unionRng)
                Else
                    Set unionRng = rng
                End If
            End If
        Next rng
    End With

    With ThisWorkbook.Worksheets("Sheet2")
        If Not unionRng Is Nothing Then unionRng.EntireRow.Copy .Cells(.Rows.count, 1).End(xlUp).Offset(1, 0)
    End With

End Sub

    Or

    Copy all data from sheet 1 to sheet 2 then just use the inbuilt data > remove duplicates and specify column A

    Remove duplicates