Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count number of occurances between 2 values | Excel Worksheet Functions | |||
Total number of occurances for two text values | Excel Worksheet Functions | |||
Finding the most frequent occurances | Excel Discussion (Misc queries) | |||
Finding multiple occurances of a date within one column: Should be easy? | Excel Programming | |||
Finding multiple occurances of a date within one column: Should be easy? | Excel Programming |