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 i
each row of my matrix and put that count (for each row) in an intege
array Present(10000)

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

--
JackRn
-----------------------------------------------------------------------
JackRnl's Profile: http://www.excelforum.com/member.php...fo&userid=3717
View this thread: http://www.excelforum.com/showthread.php?threadid=57009

Finding number of occurances of values
 

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" wrote in
message ...

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

Finding number of occurances of values
 

What you suggest is what I do now allthough I copy the whole range int
an array and loop through the array instead of retrieving each row on
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
shouldn't do that as the number of values to be tested in each row ar
small.
At the end I sort the range based on the number of "Good" for each ro

--
JackRn
-----------------------------------------------------------------------
JackRnl's Profile: http://www.excelforum.com/member.php...fo&userid=3717
View this thread: http://www.excelforum.com/showthread.php?threadid=57009