Identifying Duplicate records

[nnadimi19]

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 ray: ray: ray:

 

[SteveD]

You could run a pivot on the resulting data to only grab unique enteries...

 

[nnadimi19]

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]

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]

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? )

 

[HalfAce]

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]

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

 

[nnadimi19]

Thanks everyone

 

[timwangTM]

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