On Jun 18, 11:25 am, Toppers <Topp...@discussions.microsoft.com>
wrote:

The Data sheet contains 4 columns of data.
rngData is:
colA       colB          colC
a    1    Y
a    2    N
a    3    Y
a    1    N
a    2    Y
b    1    N
b    1    N
b    2    N
b    3    N
a    1    Y
b    2    Y
c    3    N
c    1    Y
c    2    N
c    3    Y
c    1    N
c    2    Y

rngDataNums is
colD
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

On Sheet1 the first two rows are:
colA        colB         colC
a    1    Y
b    1    N

The vba code which creates the arrays is:
Sub Test_BuildMatchList()
BuildMatchListArray3 "rngData", 1, "$A$1", "$B$1", "$C$1"
BuildMatchListArray3 "rngData", 2, "$A$2", "$B$2", "$C$2"
End Sub

Function BuildMatchListArray3(ByVal vaIn As Variant, ByVal intRowNo As
Integer, ByVal strA1 As String, ByVal strA2 As String, _
   ByVal strA3 As String)

Dim Arr() As Boolean
Dim intCount As Integer
Dim intRow As Integer
Dim intCol As Integer
Dim arstr(3) As String
Dim str1 As String
Dim str2 As String
Dim str3 As String
Dim myRange As Range

arstr(1) = Range(strA1).Value
arstr(2) = Range(strA2).Value
arstr(3) = Range(strA3).Value

ReDim Arr(16)
If Not IsMissing(vaIn) Then
Set myRange = Range(vaIn)
'Fill the array with 1 or 0 depending on whether there's a match
across the columns
For intRow = 1 To myRange.Rows.Count
   If myRange.Offset(intRow, 1).Cells(0, 0) = arstr(1) Then
       If myRange.Offset(intRow, 2).Cells(0, 0) = arstr(2) Then
           If myRange.Offset(intRow, 3).Cells(0, 0) = arstr(3) Then
               Arr(intRow - 1) = 1
           Else
               Arr(intRow - 1) = 0
           End If
       Else
           Arr(intRow - 1) = 0
   End If
   Else
       Arr(intRow - 1) = 0

   End If
Next intRow
End If

For intCount = 0 To 16
   Debug.Print Arr(intCount)
Next intCount

Names.Add Name:="Row" & intRowNo, RefersTo:=Arr

End Function

Then on Sheet1 the formula will go into column D. Once it works I need
to put it into 104 columns across and match on 8 data columns which is
why I'm trying to avoid doing the Sumproduct lookup for each cell. If
I can do the match and get the array of matching attribute rows once
per sheet1 row I'm hoping it'll speed things up a lot.

If I use =SUMPRODUCT(TRANSPOSE(Row1)*(rngDataNums)) in D1 I get 11
which is correct.
if i use =SUMPRODUCT(TRANSPOSE(Row2)*(rngDataNums)) in D2 I get 13
which is correct.

Hope that's clearer.

Thanks
John