On 24 Feb, 11:19, Joel <J...@discussions.microsoft.com> 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)=left(A1,3)&Ri*ght(A1,2)))
>
>
>
> "john.9.willi...@bt.com" 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.
|
Similar Threads
