Remove Duplicates AND ORIGINALS

[dustinisbustin]

So I have an annually acquired list of addresses that have backup generators onsite, along with contact info and company names, etc. There is a unique identifier, which is the address of the building, since these generators don't move locations often. Pretty much never. I have the list acquired last year, and the list acquired this year.
What I want to do is compare both lists, checking for duplicates, and then delete not only the duplicate line, but the original as well. What should be left is only lines that did not have a duplicate in either file. I will need the entire row containing the un matched identifier.
This should then theoretically be a list of newly installed or newly permitted generators within the last year, and the associated contact information.
I am also open to formulas that may just reference both sheets and output the NEW data into a separate file or sheet. Deletion is not necessary. I just need to have a separated list of the unique line items. Any formulas or macros that satisfy this need would be greatly appreciated.

Thank you for reading.

 

[dreid1011]

Are both sheets in the same book?

 

[kalak]

Here's a VBA code that checks a set of numbers in Col A and in Col C removes all those that occur more than once in Col A

Sub rem_all_dupes3()

Dim d As Object, a, c, i, q&
Set d = CreateObject("scripting.dictionary")
With Range(Cells(1), Cells(Rows.Count, 1).End(3))
    a = .Value
    For Each c In a
        d(c) = d(c) + 1
    Next c
    For Each c In a
        q = q + 1
        If d(c) > 1 Then a(q, 1) = vbNullString
    Next c
    .Offset(, 2) = a
End With

End Sub

Maybe of some use to you? Can be modifed as needed.

 

[dreid1011]

=IF(COUNTIF($A$1:$A$10,B1)=0,"New","")

You can put this in a column next to your list and copy down, adjust the ranges to suit your data, and it will display "New" for unique addresses.

Range A1:A10 should be on your new list (longer I would assume), and B is the old list. If they're in the same workbook, make sure you add your sheet names.

 

[dustinisbustin]

=IF(COUNTIF($A$1:$A$10,B1)=0,"New","")

You can put this in a column next to your list and copy down, adjust the ranges to suit your data, and it will display "New" for unique addresses.

Range A1:A10 should be on your new list (longer I would assume), and B is the old list. If they're in the same workbook, make sure you add your sheet names.

Thank you! I think I can make this work.

 

[dreid1011]

You're welcome.