Identify duplicates and move to new sheet

[JOUIOUI]

I have a very long worksheet that occasionally contains duplicate numbers in
Col A or duplicate numbers in col B. I'd like to copy these duplicates from
the sheet titled "AllRecords" and move them to a sheet in the existing
workbook titled, "Duplicate Records" with VBA. I've tried altering this code
and have not had any success, I can't figure out how to stipulate duplicates
in those columns. Any help you can provide is greatly appreciated. Thank you

Dim rng As Range, cell As Range

Dim i As Long, sh As Worksheet
With Worksheets("All Records")
Set rng = .Range(.Cells(1, 1), _
.Cells(Rows.Count, 1).End(xlUp))
End With
i = 1

Set sh = Worksheets("DUPLICATE RECORDS")
For Each cell In rng
cell.EntireRow.Copy sh.Cells(i, 1)
i = i + 1

End If

[markwalling@gmail.com]

the easiest, but probably most inneficient way to do this is to check
each value against all the values:

for a=1 to MAX
for b=a to MAX
if rng.cells(a,1)=rng.cells(b,1) then
'copying/moving
endif
next b
next a

JOUIOUI wrote:
> I have a very long worksheet that occasionally contains duplicate numbers in
> Col A or duplicate numbers in col B. I'd like to copy these duplicates from
> the sheet titled "AllRecords" and move them to a sheet in the existing
> workbook titled, "Duplicate Records" with VBA. I've tried altering this code
> and have not had any success, I can't figure out how to stipulate duplicates
> in those columns. Any help you can provide is greatly appreciated. Thank you
>
> Dim rng As Range, cell As Range
>
> Dim i As Long, sh As Worksheet
> With Worksheets("All Records")
> Set rng = .Range(.Cells(1, 1), _
> .Cells(Rows.Count, 1).End(xlUp))
> End With
> i = 1
>
> Set sh = Worksheets("DUPLICATE RECORDS")
> For Each cell In rng
> cell.EntireRow.Copy sh.Cells(i, 1)
> i = i + 1
>
> End If

[SITCFanTN]

I really need this to be prercise so if there is more reliable code
available, even if it is more complex, I'd appreciate using that.


Also, I'm not sure if this code would work, I need to identify duplicates in
Column A only and then check colum B for duplicates separate from A.

I appreciate your help, thanks

"markwalling@gmail.com" wrote:

> the easiest, but probably most inneficient way to do this is to check
> each value against all the values:
>
> for a=1 to MAX
> for b=a to MAX
> if rng.cells(a,1)=rng.cells(b,1) then
> 'copying/moving
> endif
> next b
> next a
>
> JOUIOUI wrote:
> > I have a very long worksheet that occasionally contains duplicate numbers in
> > Col A or duplicate numbers in col B. I'd like to copy these duplicates from
> > the sheet titled "AllRecords" and move them to a sheet in the existing
> > workbook titled, "Duplicate Records" with VBA. I've tried altering this code
> > and have not had any success, I can't figure out how to stipulate duplicates
> > in those columns. Any help you can provide is greatly appreciated. Thank you
> >
> > Dim rng As Range, cell As Range
> >
> > Dim i As Long, sh As Worksheet
> > With Worksheets("All Records")
> > Set rng = .Range(.Cells(1, 1), _
> > .Cells(Rows.Count, 1).End(xlUp))
> > End With
> > i = 1
> >
> > Set sh = Worksheets("DUPLICATE RECORDS")
> > For Each cell In rng
> > cell.EntireRow.Copy sh.Cells(i, 1)
> > i = i + 1
> >
> > End If
>
>

[markwalling@gmail.com]

the code its self is "percise", its just slow. also a and b are just
integer counters (you could use c1and c2, elephants and monkeys,
etc...)

what the code does is this:
lets say you have a list (using letters as the index):
a=1
b=4
c=3
d=1
e=3
f=3
g=2

it would start with a, and compare a to every element that followed (b,
c, d, etc) and would do the comparison on each. then it would move on
to b and compare that to c, d, e, f, etc. you can skip a because most
comparisons are commutative.

the only downside to this method is it is slow, O(n!) (number of
operations = factorial of number of terms), but it is easy to
understand, and if your list is small (relative to platform) the extra
time is insignifigant.

Mark

SITCFanTN wrote:
> I really need this to be prercise so if there is more reliable code
> available, even if it is more complex, I'd appreciate using that.
>
>
> Also, I'm not sure if this code would work, I need to identify duplicates in
> Column A only and then check colum B for duplicates separate from A.
>
> I appreciate your help, thanks
>
> "markwalling@gmail.com" wrote:
>
> > the easiest, but probably most inneficient way to do this is to check
> > each value against all the values:
> >
> > for a=1 to MAX
> > for b=a to MAX
> > if rng.cells(a,1)=rng.cells(b,1) then
> > 'copying/moving
> > endif
> > next b
> > next a
> >
> > JOUIOUI wrote:
> > > I have a very long worksheet that occasionally contains duplicate numbers in
> > > Col A or duplicate numbers in col B. I'd like to copy these duplicates from
> > > the sheet titled "AllRecords" and move them to a sheet in the existing
> > > workbook titled, "Duplicate Records" with VBA. I've tried altering this code
> > > and have not had any success, I can't figure out how to stipulate duplicates
> > > in those columns. Any help you can provide is greatly appreciated. Thank you
> > >
> > > Dim rng As Range, cell As Range
> > >
> > > Dim i As Long, sh As Worksheet
> > > With Worksheets("All Records")
> > > Set rng = .Range(.Cells(1, 1), _
> > > .Cells(Rows.Count, 1).End(xlUp))
> > > End With
> > > i = 1
> > >
> > > Set sh = Worksheets("DUPLICATE RECORDS")
> > > For Each cell In rng
> > > cell.EntireRow.Copy sh.Cells(i, 1)
> > > i = i + 1
> > >
> > > End If
> >
> >