Error while finding duplicate with Application.WorksheetFunction.Match

[roypogo]

Hi All,

I am trying to find the duplicates in a source sheet "Data" and print the duplicate values in another sheet "Data Errors".

I am using the below code :

Sub Macro3()
'
' Macro3 Macro
'


'
Dim i, j As Long


Dim LastRow, LastCol As Long
Dim DataCell, DataRange As Range
Sheets("Data").Activate
With Worksheets("Data")
LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
Set DataRange = Range(Cells(1, 1), Cells(LastRow, LastCol))



For i = 1 To LastCol
If DataRange.Cells(1, i).Value = "NM1*IL*1 - Member Name" Then
For j = 2 To LastRow
If Cells(j, i) <> "" Then
matchFoundIndex = Application.WorksheetFunction.Match(Cells(j, i), Range(Cells(1, i), Cells(LastRow, i)), 0)
If j <> matchFoundIndex Then
tmp = Sheet1.Cells(j, i).Value
With Worksheets("Data Errors")
.Cells(.Rows.Count, "A").End(xlUp).Offset(1).Value = "Duplicate in Row " & j
.Cells(.Rows.Count, "B").End(xlUp).Offset(1).Value = tmp
End With
End If
End If
Next j
End If
Next i
End With
End Sub


When my data is as below it is giving me correct output:

Source:
[TABLE="width: 168"]
<tbody>[TR]
[TD]NM1*IL*1 - Member Name[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]a[/TD]
[/TR]
[TR]
[TD]b[/TD]
[/TR]
[TR]
[TD]c[/TD]
[/TR]
[TR]
[TD]a[/TD]
[/TR]
[TR]
[TD]c[/TD]
[/TR]
[TR]
[TD]d[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]13

Output:
[TABLE="width: 187"]
<tbody>[TR]
[TD]Duplicate in Row 4[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Duplicate in Row 8[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]Duplicate in Row 9[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]Duplicate in Row 13[/TD]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


But if my data is as below:

[TABLE="width: 439"]
<tbody>[TR]
[TD]NM1*IL*1 - Member Name[/TD]
[/TR]
[TR]
[TD]NM1*IL*1*AARON*ADAM*C***34*11111~[/TD]
[/TR]
[TR]
[TD]NM1*IL*1*ABATE*CHARLES*A***34*11111~[/TD]
[/TR]
[TR]
[TD]NM1*IL*1*ABBETT*CARRIE*M***34*11111~[/TD]
[/TR]
[TR]
[TD]NM1*IL*1*ABBEY*HERSCHEL*D***34*11111~[/TD]
[/TR]
[TR]
[TD]NM1*IL*1*ABBOTT*MELISSA*B***34*11111~[/TD]
[/TR]
[TR]
[TD]NM1*IL*1*ABBOTT*LEWIS*M***34*11111~[/TD]
[/TR]
[TR]
[TD]NM1*IL*1*ABDAL-SABUR*JAWWAAD****34*11111~[/TD]
[/TR]
[TR]
[TD]NM1*IL*1*ABED*AARON*D***34*28~[/TD]
[/TR]
[TR]
[TD]NM1*IL*1*ABED*AARON*D***34*28~


It is giving me error as :

Run time error '1004'
Unable to get the Match property of the WorksheetFunction Class


Need help !!![/TD]
[/TR]
</tbody>[/TABLE]

 

[wideboydixon]

Try using Application.Match instead. Then check that IsError(matchFoundIndex) is False.

WBD

 

[roypogo]

Well I figured out the "~" is the data is causing the problem.
Can anyone help me out how to tweak my code above to get rid of the "~" in the data.

I am using the below code:

Sub Macro1()
'
' Macro1 Macro
'


'
Dim i, j As Long




Dim LastRow, LastCol As Long
Dim DataCell, DataRange As Range
Sheets("Data").Activate
With Worksheets("Data")
LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
Set DataRange = Range(Cells(1, 1), Cells(LastRow, LastCol))






For i = 1 To LastCol
If DataRange.Cells(1, i).Value = "NM1*IL*1 - Member Name" Then
For j = 2 To LastRow
If Cells(j, i) <> "" Then
matchFoundIndex = Application.Match(Cells(j, i), Range(Cells(1, i), Cells(LastRow, i)), 0)
If IsError(matchFoundIndex) Then
'not found
Else
If j <> matchFoundIndex Then
tmp = Sheet1.Cells(j, i).Value
With Worksheets("Data Errors")
.Cells(.Rows.Count, "A").End(xlUp).Offset(1).Value = "Duplicate in Row " & j
.Cells(.Rows.Count, "B").End(xlUp).Offset(1).Value = tmp
End With
End If
End If
End If
Next j
End If
Next i
End With
End Sub

 

[JackDanIce]

As far as I can tell, you're finding column headers of "NM1*IL*1 - Member Name", then listing the duplicates found in that column in sheet Data Error with the row number it occurs on. Is there ever more than one column with this header?

 

[JackDanIce]

And can there be more than 2 duplicates i.e. 3 or more?

 

[Marcelo Branco]

Yes, the "~" is the culprit
Try changing this

matchFoundIndex = Application.WorksheetFunction.Match(Cells(j, i), Range(Cells(1, i), Cells(LastRow, i)), 0)

by

matchFoundIndex = Evaluate("=MATCH(SUBSTITUTE(" & .Cells(j, i).Address & ",""~"",""""),SUBSTITUTE(" _
& Range(.Cells(1, i), .Cells(LastRow, i)).Address & ",""~"",""""),0)")

Hope this helps

M.

 

[roypogo]

Yes - I am first working on Worksheet "Data" where I am trying to find the column which contains the header "NM1*IL*1 - Member Name" and then trying to find the duplicates in that column. Once found I am putting the rownum and the value (of Worksheet "Data") in another worksheet "Data Errors".
The rownum can be the first occurence or the last occurence.

Can there be more than 2 duplicates i.e. 3 or more - Yes.

I am having data which are ending with ~ which is causing the error.



Thanks

 

[Marcelo Branco]

I am having data which are ending with ~ which is causing the error.

Have you seen post #6 ?

M.

 

[Marcelo Branco]

Have you seen post #6 ?

M.

oops...
Forgot . before Evaluate

try
.Evaluate("=MATCH(..................)")

M.

 

[roypogo]

It worked !!!!

Thanks