compaing data in columns

[john.9.williams]

Hi,

I have two sets of data, data in sheet 1 column a has about 400 rows
in each row there is data with wild cards, sheet 2 have about 4000
rows and has the data without the wild cards, i want to be able to
what data in sheet2 appears in sheet 1, cannot seem to get this to
work, example data shown below

Sheet 1 Sheet 2

APAXX12 APADR12
APAXX34 APATY89
APRXX89 APRJNDT
APRXXDT

x Is the wild card in sheet1

Please Help i am going crazy!!

 

[Joel]

Try this code. You will get 0 if there arre no matches, otherwise, the
result will be the number of matches

=Sumproduct(--(left(Sheet1!A1:A100,3)&Right(Sheet1!A1:A100,2)=left(A1,3)&Right(A1,2)))

 

[Geoffrey]

Try this code.  You will get 0 if there arre no matches, otherwise, the
result will be the number of matches

=Sumproduct(--(left(Sheet1!A1:A100,3)&Right(Sheet1!A1:A100,2)=left(A1,3)&Ri­ght(A1,2)))










- Show quoted text -

There may be a more elegant way to do this in code and I'm sure somone
will say so if there is, but I would do it like this:

If code2 Like searchTerm(code1) Then <your action>

Where searchTerm is a function that converts your Xs into VBA ?
wildcards:

Function searchTerm(rawTerm As String) As String
Dim i As Integer

For i = 1 To Len(rawTerm)
If Mid(rawTerm, i, 1) = "X" Then
searchTerm = searchTerm & "?"
Else
searchTerm = searchTerm & Mid(rawTerm, i, 1)
End If
Next

End Function


This function assumes that your Xs will always be upper case but it
allows them to be anywhere in the string.