Finding number of occurances of values

I have a sorted array (8 integer elements) and a large matrix (10000, 9) and would like to know how many elements of my sorted array occur in each row of my matrix and put that count (for each row) in an integer array Present(10000)

Must i compare each element of my sorted array with each element of each row of my matrix (OK, I can do it "intelligent" and skip part of the comparisons), or does a function exist that allows me to do that more efficient. Ideal would be for the whole matrix at once of course.

I would do that with VBA but perhaps someone else can come up with an array
formula for you.
In VBA, you would write code to loop through each row of your 10000 rows.
Within this loop, you would have a second loop through the sorted array of
8. The inner loop would come up with the number of "hits" for each row and
place that number in the 10th column. Does that sound like something you
want? Post back if you need more. HTH Otto
"JackRnl" <[email protected]> wrote in
message news:[email protected]...
>
> I have a sorted array (8 integer elements) and a large matrix (10000, 9)
> and would like to know how many elements of my sorted array occur in
> each row of my matrix and put that count (for each row) in an integer
> array Present(10000)
>
> Must i compare each element of my sorted array with each element of
> each row of my matrix (OK, I can do it "intelligent" and skip part of
> the comparisons), or does a function exist that allows me to do that
> more efficient. Ideal would be for the whole matrix at once of course.
>
>
> --
> JackRnl
> ------------------------------------------------------------------------
> JackRnl's Profile:
> http://www.excelforum.com/member.php...o&userid=37172
> View this thread: http://www.excelforum.com/showthread...hreadid=570095
>

What you suggest is what I do now allthough I copy the whole range into an array and loop through the array instead of retrieving each row one at a time
Dim LastRow As Integer
Dim Good As Integer
Set rngSets = ActiveCell.CurrentRegion
LastRow = rngSets.Rows.Count
ColGood = NumColumns + 1
Set rngGood = Range(.Cells(1, ColGood), .Cells(LastRow, ColGood))
rngGood.Value = Empty
Set rngSets = ActiveCell.CurrentRegion '// Needed
arrSets = rngSets.Value
ReDim Preserve arrSets(LastRow, NumColumns)

ReDim arrGood(LastRow) As Integer
for k = 1 to LastRow
Good = 0
n = 1
for l = 1 to NumColumns
ValTest = arrTest(l)
For m = n To NumColumns
If ValTest < arrSets(k, m) Then
n = m
Exit For
ElseIf ValTest = arrSets(k, m) Then
Good = Good + 1
n = m + 1
Exit For
End If
next m
Next l
arrGood(k) = Good
next k

rngGood = Application.Transpose(arrGood)
rngSets = arrSets
with Activecell.currentselection
.Sort Key1:=Range(.Cells(1, ColGood).Address()), Header:=xlNo, OrderCustom:=1, Orientation:=xlTopToBottom
end with

as you see I also try not to test all values again . It could be I shouldn't do that as the number of values to be tested in each row are small.
At the end I sort the range based on the number of "Good" for each row