Remove Duplicates AND ORIGINALS

dustinisbustin

New Member
Joined
Aug 31, 2015
Messages
2
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.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

kalak

Active Member
Joined
Jun 10, 2015
Messages
487
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
Code:
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

Well-known Member
Joined
Jun 4, 2015
Messages
2,132
=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

New Member
Joined
Aug 31, 2015
Messages
2
=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.
 

Forum statistics

Threads
1,136,845
Messages
5,678,092
Members
419,742
Latest member
Dropzyl88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top