Is it a duplicate row?
 

I am trying to determine if a row is a duplicate based on two criteria. This
is what I have so far, but it doesn't work. Any pointers much appreciated!

sub tester
Dim iLastRow As Long
Dim i As Long
Dim rName1 As Range
Dim rName2 As Range
Dim rTestRng As Range

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
' it's a dup if there are two rows in which columns C match AND
columns J ' match
Set rTestRng = Union(.Cells(i, "c"), .Cells(i, "j"))
If Application.CountIf(Union(.Range("c1:c" & iLastRow),
..Range("j1:j" & iLastRow)), rTestRng) = 2 Then
' do some stuff here
else ' do something else
end if
next i

end sub

Is it a duplicate row?
 

Sub tester()
Dim iLastRow As Long
Dim i As Long
Dim rName1 As Range
Dim rName2 As Range
Dim rTestRng As Range

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
'it's a dup if there are two rows in which columns C match AND Columns J
match
If Evaluate("SUMPRODUCT(--(C1:C" & iLastRow & "=" & .Cells(i,
"C").Value & ")," & _
"--(J1:J" & iLastRow & "=" & .Cells(i,
"J").Value & "))") 1 Then
' do some stuff here
Else ' do something else
End If
Next i

End Sub

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"GettingThere" wrote in message
...
I am trying to determine if a row is a duplicate based on two criteria.
This
is what I have so far, but it doesn't work. Any pointers much
appreciated!

sub tester
Dim iLastRow As Long
Dim i As Long
Dim rName1 As Range
Dim rName2 As Range
Dim rTestRng As Range

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
' it's a dup if there are two rows in which columns C match AND
columns J ' match
Set rTestRng = Union(.Cells(i, "c"), .Cells(i, "j"))
If Application.CountIf(Union(.Range("c1:c" & iLastRow),
.Range("j1:j" & iLastRow)), rTestRng) = 2 Then
' do some stuff here
else ' do something else
end if
next i

end sub

Is it a duplicate row?
 

Thanks Bob.

I'm getting a type mismatch error on the "evaluate" line. Could it be
because column J contains dates?



"Bob Phillips" wrote:

Sub tester()
Dim iLastRow As Long
Dim i As Long
Dim rName1 As Range
Dim rName2 As Range
Dim rTestRng As Range

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
'it's a dup if there are two rows in which columns C match AND Columns J
match
If Evaluate("SUMPRODUCT(--(C1:C" & iLastRow & "=" & .Cells(i,
"C").Value & ")," & _
"--(J1:J" & iLastRow & "=" & .Cells(i,
"J").Value & "))") 1 Then
' do some stuff here
Else ' do something else
End If
Next i

End Sub

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"GettingThere" wrote in message
...
I am trying to determine if a row is a duplicate based on two criteria.
This
is what I have so far, but it doesn't work. Any pointers much
appreciated!

sub tester
Dim iLastRow As Long
Dim i As Long
Dim rName1 As Range
Dim rName2 As Range
Dim rTestRng As Range

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
' it's a dup if there are two rows in which columns C match AND
columns J ' match
Set rTestRng = Union(.Cells(i, "c"), .Cells(i, "j"))
If Application.CountIf(Union(.Range("c1:c" & iLastRow),
.Range("j1:j" & iLastRow)), rTestRng) = 2 Then
' do some stuff here
else ' do something else
end if
next i

end sub

Is it a duplicate row?
 

Is your file sorted either ascending or descendingorder? If so maybe you can
use this one:

Sub DupeDel()
Set currentCell = Worksheets(YourSheetNumber).Range("C1")
Do While Not IsEmpty(currentCell)
Set nextCell = currentCell.Offset(1, 0)
If nextCell.Value = currentCell.Value And
currentCell.Offset(0, 7).Value = currentCell.Offset(1, 7).Value Then
currentCell.EntireRow.Delete
End If
Set currentCell = nextCell
Loop
End Sub

It only works on files that are sorted, but if you do not want to delete the
files you can modify it to count the occurences.

"GettingThere" wrote:

Thanks Bob.

I'm getting a type mismatch error on the "evaluate" line. Could it be
because column J contains dates?



"Bob Phillips" wrote:

Sub tester()
Dim iLastRow As Long
Dim i As Long
Dim rName1 As Range
Dim rName2 As Range
Dim rTestRng As Range

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
'it's a dup if there are two rows in which columns C match AND Columns J
match
If Evaluate("SUMPRODUCT(--(C1:C" & iLastRow & "=" & .Cells(i,
"C").Value & ")," & _
"--(J1:J" & iLastRow & "=" & .Cells(i,
"J").Value & "))") 1 Then
' do some stuff here
Else ' do something else
End If
Next i

End Sub

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"GettingThere" wrote in message
...
I am trying to determine if a row is a duplicate based on two criteria.
This
is what I have so far, but it doesn't work. Any pointers much
appreciated!

sub tester
Dim iLastRow As Long
Dim i As Long
Dim rName1 As Range
Dim rName2 As Range
Dim rTestRng As Range

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
' it's a dup if there are two rows in which columns C match AND
columns J ' match
Set rTestRng = Union(.Cells(i, "c"), .Cells(i, "j"))
If Application.CountIf(Union(.Range("c1:c" & iLastRow),
.Range("j1:j" & iLastRow)), rTestRng) = 2 Then
' do some stuff here
else ' do something else
end if
next i

end sub

Is it a duplicate row?
 

Could well be.

Try this version, I have also assumed that column C is test not numeric

Sub tester()
Dim iLastRow As Long
Dim i As Long
Dim rName1 As Range
Dim rName2 As Range
Dim rTestRng As Range

With ActiveSheet
iLastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For i = iLastRow To 1 Step -1
'it's a dup if there are two rows in which columns C match AND Columns J
match
If Evaluate("SUMPRODUCT(--(C1:C" & iLastRow & "=""" & .Cells(i,
"C").Value & """)," & _
"--(J1:J" & iLastRow & "=--""" &
Format(.Cells(i, "J").Value, "yyyy-mm-dd") & """))") 1 Then
' do some stuff here
Else ' do something else
End If
Next i
End With

End Sub


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"GettingThere" wrote in message
...
Thanks Bob.

I'm getting a type mismatch error on the "evaluate" line. Could it be
because column J contains dates?



"Bob Phillips" wrote:

Sub tester()
Dim iLastRow As Long
Dim i As Long
Dim rName1 As Range
Dim rName2 As Range
Dim rTestRng As Range

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
'it's a dup if there are two rows in which columns C match AND
Columns J
match
If Evaluate("SUMPRODUCT(--(C1:C" & iLastRow & "=" & .Cells(i,
"C").Value & ")," & _
"--(J1:J" & iLastRow & "=" & .Cells(i,
"J").Value & "))") 1 Then
' do some stuff here
Else ' do something else
End If
Next i

End Sub

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"GettingThere" wrote in message
...
I am trying to determine if a row is a duplicate based on two
criteria.
This
is what I have so far, but it doesn't work. Any pointers much
appreciated!

sub tester
Dim iLastRow As Long
Dim i As Long
Dim rName1 As Range
Dim rName2 As Range
Dim rTestRng As Range

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
' it's a dup if there are two rows in which columns C match
AND
columns J ' match
Set rTestRng = Union(.Cells(i, "c"), .Cells(i, "j"))
If Application.CountIf(Union(.Range("c1:c" &
iLastRow),
.Range("j1:j" & iLastRow)), rTestRng) = 2 Then
' do some stuff here
else ' do something else
end if
next i

end sub

Is it a duplicate row?
 

Hi Bob,

I'm sorry that I couldn't reply sooner...

The code below works wonderfully, & I really appreciate your help!



"Bob Phillips" wrote:

Could well be.

Try this version, I have also assumed that column C is test not numeric

Sub tester()
Dim iLastRow As Long
Dim i As Long
Dim rName1 As Range
Dim rName2 As Range
Dim rTestRng As Range

With ActiveSheet
iLastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For i = iLastRow To 1 Step -1
'it's a dup if there are two rows in which columns C match AND Columns J
match
If Evaluate("SUMPRODUCT(--(C1:C" & iLastRow & "=""" & .Cells(i,
"C").Value & """)," & _
"--(J1:J" & iLastRow & "=--""" &
Format(.Cells(i, "J").Value, "yyyy-mm-dd") & """))") 1 Then
' do some stuff here
Else ' do something else
End If
Next i
End With

End Sub


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"GettingThere" wrote in message
...
Thanks Bob.

I'm getting a type mismatch error on the "evaluate" line. Could it be
because column J contains dates?



"Bob Phillips" wrote:

Sub tester()
Dim iLastRow As Long
Dim i As Long
Dim rName1 As Range
Dim rName2 As Range
Dim rTestRng As Range

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
'it's a dup if there are two rows in which columns C match AND
Columns J
match
If Evaluate("SUMPRODUCT(--(C1:C" & iLastRow & "=" & .Cells(i,
"C").Value & ")," & _
"--(J1:J" & iLastRow & "=" & .Cells(i,
"J").Value & "))") 1 Then
' do some stuff here
Else ' do something else
End If
Next i

End Sub

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"GettingThere" wrote in message
...
I am trying to determine if a row is a duplicate based on two
criteria.
This
is what I have so far, but it doesn't work. Any pointers much
appreciated!

sub tester
Dim iLastRow As Long
Dim i As Long
Dim rName1 As Range
Dim rName2 As Range
Dim rTestRng As Range

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
' it's a dup if there are two rows in which columns C match
AND
columns J ' match
Set rTestRng = Union(.Cells(i, "c"), .Cells(i, "j"))
If Application.CountIf(Union(.Range("c1:c" &
iLastRow),
.Range("j1:j" & iLastRow)), rTestRng) = 2 Then
' do some stuff here
else ' do something else
end if
next i

end sub

Is it a duplicate row?
 

Not a problem. Glad it helped.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"GettingThere" wrote in message
...
Hi Bob,

I'm sorry that I couldn't reply sooner...

The code below works wonderfully, & I really appreciate your help!



"Bob Phillips" wrote:

Could well be.

Try this version, I have also assumed that column C is test not numeric

Sub tester()
Dim iLastRow As Long
Dim i As Long
Dim rName1 As Range
Dim rName2 As Range
Dim rTestRng As Range

With ActiveSheet
iLastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For i = iLastRow To 1 Step -1
'it's a dup if there are two rows in which columns C match AND
Columns J
match
If Evaluate("SUMPRODUCT(--(C1:C" & iLastRow & "=""" & .Cells(i,
"C").Value & """)," & _
"--(J1:J" & iLastRow & "=--""" &
Format(.Cells(i, "J").Value, "yyyy-mm-dd") & """))") 1 Then
' do some stuff here
Else ' do something else
End If
Next i
End With

End Sub


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"GettingThere" wrote in message
...
Thanks Bob.

I'm getting a type mismatch error on the "evaluate" line. Could it be
because column J contains dates?



"Bob Phillips" wrote:

Sub tester()
Dim iLastRow As Long
Dim i As Long
Dim rName1 As Range
Dim rName2 As Range
Dim rTestRng As Range

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
'it's a dup if there are two rows in which columns C match AND
Columns J
match
If Evaluate("SUMPRODUCT(--(C1:C" & iLastRow & "=" &
..Cells(i,
"C").Value & ")," & _
"--(J1:J" & iLastRow & "=" &
..Cells(i,
"J").Value & "))") 1 Then
' do some stuff here
Else ' do something else
End If
Next i

End Sub

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"GettingThere" wrote in
message
...
I am trying to determine if a row is a duplicate based on two
criteria.
This
is what I have so far, but it doesn't work. Any pointers much
appreciated!

sub tester
Dim iLastRow As Long
Dim i As Long
Dim rName1 As Range
Dim rName2 As Range
Dim rTestRng As Range

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
' it's a dup if there are two rows in which columns C
match
AND
columns J ' match
Set rTestRng = Union(.Cells(i, "c"), .Cells(i, "j"))
If Application.CountIf(Union(.Range("c1:c" &
iLastRow),
.Range("j1:j" & iLastRow)), rTestRng) = 2 Then
' do some stuff here
else ' do something else
end if
next i

end sub

Is it a duplicate row?
 

I was about to ask the same question thanks for the help.

Martin

"Bob Phillips" wrote in message
...
Not a problem. Glad it helped.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"GettingThere" wrote in message
...
Hi Bob,

I'm sorry that I couldn't reply sooner...

The code below works wonderfully, & I really appreciate your help!



"Bob Phillips" wrote:

Could well be.

Try this version, I have also assumed that column C is test not
numeric

Sub tester()
Dim iLastRow As Long
Dim i As Long
Dim rName1 As Range
Dim rName2 As Range
Dim rTestRng As Range

With ActiveSheet
iLastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For i = iLastRow To 1 Step -1
'it's a dup if there are two rows in which columns C match AND
Columns J
match
If Evaluate("SUMPRODUCT(--(C1:C" & iLastRow & "=""" &
..Cells(i,
"C").Value & """)," & _
"--(J1:J" & iLastRow & "=--""" &
Format(.Cells(i, "J").Value, "yyyy-mm-dd") & """))") 1 Then
' do some stuff here
Else ' do something else
End If
Next i
End With

End Sub


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"GettingThere" wrote in
message
...
Thanks Bob.

I'm getting a type mismatch error on the "evaluate" line. Could it
be
because column J contains dates?



"Bob Phillips" wrote:

Sub tester()
Dim iLastRow As Long
Dim i As Long
Dim rName1 As Range
Dim rName2 As Range
Dim rTestRng As Range

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
'it's a dup if there are two rows in which columns C match AND
Columns J
match
If Evaluate("SUMPRODUCT(--(C1:C" & iLastRow & "=" &
.Cells(i,
"C").Value & ")," & _
"--(J1:J" & iLastRow & "=" &
.Cells(i,
"J").Value & "))") 1 Then
' do some stuff here
Else ' do something else
End If
Next i

End Sub

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"GettingThere" wrote in
message
...
I am trying to determine if a row is a duplicate based on two
criteria.
This
is what I have so far, but it doesn't work. Any pointers much
appreciated!

sub tester
Dim iLastRow As Long
Dim i As Long
Dim rName1 As Range
Dim rName2 As Range
Dim rTestRng As Range

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
' it's a dup if there are two rows in which columns C
match
AND
columns J ' match
Set rTestRng = Union(.Cells(i, "c"), .Cells(i, "j"))
If Application.CountIf(Union(.Range("c1:c" &
iLastRow),
.Range("j1:j" & iLastRow)), rTestRng) = 2 Then
' do some stuff here
else ' do something else
end if
next i

end sub












----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----