Solved: Dual Worksheet Duplicates

[j19_2002]

My problem is:
(This is all in the same workbook)
I have a set of numbers on sheet1 in column A.
Theres 4000+ acct #'s in that column & each set in its own cell
ex:
A1= 9463512658
A2= 7351685932
and it goes on in that format until around A4000


I have a 2nd set of acct #'s on sheet2 in column A, about 11k+ entries. But the acct #'s are all different from the 1st sheet, well at least are suppose to be.

What im tryin to have happen is automatically scan all acct #'s on sheet1 cell by cell and look for a duplicate entry on sheet2; once a duplicate is found, put that duplicate entry onto sheet 3 but not delete it from either sheets just copy the duplicate ande paste it on sheet3.

I know basic VBA but this is far too complex for me, and hence why I am
asking for help.

I have attached an example of how the real sheets look and how there set up. I know I could sort them but remember there 11k+entries on the original and there isnt suppose to be duplicate entries on the 2nd sheet compared to the 1st.

Thanks in advance to anyonewho is willing to help!!

[Bob Phillips]

[vba]

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim NextRow As Long
Dim shMatch As Worksheet
Dim shCopy As Worksheet

Set shMatch = Worksheets("Sheet2")
Set shCopy = Worksheets("Sheet3")
With Worksheets("Sheet1")

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow

If Not IsError(Application.Match(.Cells(i, "A").Value, shMatch.Columns(1), 0)) Then

NextRow = NextRow + 1
.Cells(i, "A").Copy shCopy.Cells(NextRow, "A")
End If
Next i

End With
End Sub
[/vba]

[j19_2002]

It works flawlessly!!!

P.S. There wasnt supposed to be any duplicte entries, there was around 1500... =)

Man your a great help, thanks so much. I'll be posting future problems for certain!!!

[j19_2002]

ok theres an extra part I need help with...sorry!!! =(

is there anway to take the whole row instead of that one cell and copy the whole row.

Both sheets have added information in rows B,C,D,E,F
like names, addresses, contact information basically.

[Bob Phillips]

Change

[vba]

.Cells(i, "A").Copy shCopy.Cells(NextRow, "A")
[/vba]

to

[vba]

.Rows(i).Copy shCopy.Cells(NextRow, "A")
[/vba]

[j19_2002]

Thank you for all your help, just found out I was doing the copying wrong.... What I needed to have happen, because there is so much data inputed, I needed to cut the dups off of sheet 2 than copy the cut cell to sheet3 that way I have clean acct #'s on sheet 2 to work off that I know are new.

Is there a link where I can donate to this website because this has been tons of help. I love this site!!! Thank you so much!!

[Bob Phillips]

No. It is a free service given by volunteers.

If you want to give, develop your skills, get good, and start helping others. That's the way it works best.

[j19_2002]

Thank You XLD,

I tried re-writing the code given to me previously to cut the duplicates on sheet2 and than paste it onto sheet 3 that way sheet2 now only has new entries left. It wouldnt work for me, cuz it would cut dups on sheet1 but they were incorrect. Please help. Thanks so much!

Believe me, im looking at your code and just learning bit by bit. Helps alot!

[Bob Phillips]

AT the moment, the code identifies those items on Sheet1 that are duplicated on Sheet2. Are you saying you would like it to identify those items on Sheet2 that are duplicated on Sheet1 instead, and move those over to Sheet3?

[j19_2002]

No I want it to keep identifying those items on sheet 1 that are duplicated on sheet 2 as it is doing but instead of just identifying it can it be cut or deleted off of sheet2 than just pasted to sheet3?? That way after its done deleting or cutting the duplicated items on sheet 2 the only thing remaining are new acct's that havent been duplicated.

[mikerickson]

[deleted by poster]