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!!

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)=le ft(A1,3)&Right(A1,2)))

" wrote:

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!!

On 24 Feb, 11:19, Joel wrote:
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)=le ft(A1,3)&Ri*ght(A1,2)))



" wrote:
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!!- Hide quoted text -

- 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.