Two of my worksheets (there are 10 total worksheets)in my workbook titled
"All Records" are titled "Confirm No Match" and the other titled "Payments No
Match". Each of these tables have columns titlted, "Name" and "Amount". I
would like to copy any duplicate records in the two worksheets with the same
"Amount" field and "Name" fields and copy them two a worksheet titled "Name
Wildcard". Because many of the names are misspelled, I was wondering how I
could use a single wildcard character. For example if the name was in the
Confirm No Match as "Ouimay" and in the Payment No Match as "Ouimey" and both
dollar amounts were $100.00, the row would be copied to the Name Wildcard
worksheet.

I looked at the other posts and in help and it seem that one has to specify
the location of the wildcard, I just want it to be any character or even
better if we could specify to match the name with 2 wildcards for more hits,
is that even possible.

Not sure if this will help you, but here is a function which compares the
contents of 2 cells and returns a percentage (a double) indicating how
similar they are.

Public Function Equivalence(rng1 As Range, rng2 As Range) As Double
Dim MtchTbl(100, 100)
Dim MyMax As Double, ThisMax As Double
Dim i As Integer, j As Integer, ii As Integer, jj As Integer
Dim st1 As String, st2 As String
If (rng1.Count 1) Or (rng2.Count 1) Then
MsgBox "Arguments for Equivalence function must be individual
cells", _
vbExclamation, "Equivalence error"
Equivalence = -1
End If
st1$ = Trim(LCase(rng1.Value))
st2$ = Trim(LCase(rng2.Value))
MyMax# = 0
For i% = Len(st1$) To 1 Step -1
For j% = Len(st2$) To 1 Step -1
If Mid(st1$, i%, 1) = Mid(st2$, j%, 1) Then
ThisMax# = 0
For ii% = (i% + 1) To Len(st1$)
For jj% = (j% + 1) To Len(st2$)
If MtchTbl(ii%, jj%) ThisMax# Then
ThisMax# = MtchTbl(ii%, jj%)
End If
Next jj%
Next ii%
MtchTbl(i%, j%) = ThisMax# + 1
If (ThisMax# + 1) ThisMax# Then
MyMax# = ThisMax# + 1
End If
End If
Next j%
Next i%
Equivalence# = MyMax# / ((Len(st1$) + Len(st2$)) / 2)
End Function

Try it and see it if can help you match names which are close, but not
exactly the same.

Hope this helps,

Hutch

"JOUIOUI" wrote:

Two of my worksheets (there are 10 total worksheets)in my workbook titled
"All Records" are titled "Confirm No Match" and the other titled "Payments No
Match". Each of these tables have columns titlted, "Name" and "Amount". I
would like to copy any duplicate records in the two worksheets with the same
"Amount" field and "Name" fields and copy them two a worksheet titled "Name
Wildcard". Because many of the names are misspelled, I was wondering how I
could use a single wildcard character. For example if the name was in the
Confirm No Match as "Ouimay" and in the Payment No Match as "Ouimey" and both
dollar amounts were $100.00, the row would be copied to the Name Wildcard
worksheet.

I looked at the other posts and in help and it seem that one has to specify
the location of the wildcard, I just want it to be any character or even
better if we could specify to match the name with 2 wildcards for more hits,
is that even possible.