Identifying Duplicate records

nnadimi19

Board Regular
Joined
Jul 14, 2002
Messages
240
I have a sheet that I will call my main sheet. This sheet has four columns Name, address1, addres2 and Taxid. I have another sheet that I will call my sub sheet. This sub sheet also has the same columns as the main sheet. I want to add records from the sub sheet to the main sheet, but I want to make sure that I am not adding any duplicates. I need to check the sub sheet against the main sheet and identify the duplicates of any columns in the main sheet.

Please :pray: :pray: :pray: :rolleyes: :rolleyes:
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

SteveD

Board Regular
Joined
Feb 20, 2002
Messages
104
You could run a pivot on the resulting data to only grab unique enteries...
 

nnadimi19

Board Regular
Joined
Jul 14, 2002
Messages
240
Steve;

Thanks for the quick response. But unfortunately I do not understand the answer. Could you please go into a bit more detail or maybe an exaple.

Thanks
 

SteveD

Board Regular
Joined
Feb 20, 2002
Messages
104
Select your data, then click on DATA-> Pivot Table..
A window will pop up, click next, the after verifying that the range is the range you want to look at, click next then click on layout.

In this window, drag the data into the rows section, one on top of the other placing the least dependent data on top of the more dependant data (place name above address incase there are multiple addresses for the same name). Drag something that is not important for your sort into the data section in the middle of the pivot. Click ok. Then click finish.

You will end up with a pivot table on a new spread sheet. In this pivot, your names and addresses will be listed only once. Then you can copy the pivot table (be sure to use ctrl+c to copy as the right click is not available) and paste special values to some other point on the spreadsheet.

It is a bit of work, and a little un-obvious, but after working with it you’ll be able to get the results you are looking for.
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454

ADVERTISEMENT

Another option (which should result in less work each time) might be to try this.
I'm not an accountant, but I have assumed the taxid's will all be unique.
Assumes your data (on both sheets) is in columns A:D with the taxid being column D.
Assumes your sheets are actually named "main" and "sub"
Assumes the taxid values are Constants. (as in not formulas, but just strings.)

1. Install a button on your sub sheet. (Forms toolbar button will work fine for this.)
2. Assign this code to the button:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> CheckForDuplicateData()
<SPAN style="color:#007F00">'Button goes on sub sheet</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> Dupe <SPAN style="color:#00007F">As</SPAN> Range

<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> taxid <SPAN style="color:#00007F">In</SPAN> ActiveSheet.Range("D2:D65536").SpecialCells(xlCellTypeConstants, 3)
<SPAN style="color:#00007F">Set</SPAN> Dupe = Sheets("main").Range("D2:D65536").Find(taxid, lookat:=xlContents)
<SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Dupe <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
    MsgBox "TaxId  '" & Dupe & "'  already exists in the main sheet.", vbInformation, "Duplicate Data"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> taxid

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


You would simply use this routine before copying to the main sheet. (That could even be included if there were no duplicates found...)

Hope it helps,
Dan

(Hey Nimrod, this any better than before? :cool: )
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Howdy, got bored and decided to include the copying part of your requirements. Only happens if there is no duplicate data found.
(All previous assumptions still apply.)


<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> CheckAndCopy()
<SPAN style="color:#00007F">Dim</SPAN> Dupe <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
c = ActiveSheet.Range("A2", Range("IV2").End(xlToLeft)).Columns.Count
r = ActiveSheet.Range("A2", Range("A65536").End(xlUp)).Rows.Count

<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> taxid <SPAN style="color:#00007F">In</SPAN> ActiveSheet.Range("D2:D65536").SpecialCells(xlCellTypeConstants, 3)
<SPAN style="color:#00007F">Set</SPAN> Dupe = Sheets("main").Range("D2:D65536").Find(taxid, lookat:=xlContents)
<SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Dupe <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
    MsgBox "TaxId  '" & Dupe & "'  already exists in the main sheet.", vbInformation, "Duplicate Data"
    <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> taxid

Sheets("main").Range("A65536").End(xlUp)(2, 1).Resize(r, c).Value = Sheets("sub").Range(Cells(2, 1), Cells(r + 1, c)).Value
<SPAN style="color:#007F00">'Sheets("sub").Range(Cells(2, 1), Cells(r + 1, c)).Clear</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


Note, remove the apostrophe in front of the line
<font face=Courier New><SPAN style="color:#007F00">'Sheets("sub").Range(Cells(2, 1), Cells(r + 1, c)).Clear</SPAN></FONT>
if you want to clear out from the sub sheet, the entries that just got copied to the main sheet. You can either leave it alone or remove that entire line if you'll never want to do that.

Dan
 

brettdj

Active Member
Joined
Feb 5, 2003
Messages
426

ADVERTISEMENT

If your column position is identical in both main and sub sheets then you use my addin from here to delete or highlight your duplicate rows

The addin runs over sheets from left to right so your sub sheet should be positioned to the right of the main sheet so that the addin "knows" that the main sheet contains the orginals and the sub sheet the dupes

select the four columns over both sheets
run the addin
Application Scope - select 'Range'
Search Option - 'Row Search'
Output Option - 'Highlight Duplicates'
hit ok

The addin will highlight rows with duplicate data in those four selected columns in both sheets.

Please save your work before running the code

Cheers

Dave
 

timwangTM

New Member
Joined
Jan 12, 2005
Messages
44
MS Query can also serve this task very well. MS query will automatically filter out all unique records. The SQL text like following:

------------------------------------
Select * From MainSheet
UNION
Select * From SubSheet
-------------------------------------

If you save it as query file (*.qry), at any time when you click the query file and then it bring the result into Excel. Or you can setup the query directly into your excel so as to "refresh" it to retrieve the latest result.

Thanks,
Tim
 

Watch MrExcel Video

Forum statistics

Threads
1,122,331
Messages
5,595,541
Members
413,996
Latest member
mabelO

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