Arrays – counting occurences

Topic

New Reply

Hi,

Say I have an array (80,10)

In the third ‘column’ in dimension two (1,3 – 2,3 etc). I’m comparing the values to another array.

Are there similar to Excels ‘counta’ function a way to count number of occurences of a specific value in that particular element or do I have to loop the entire array. (eg. looking for the number of times the figure ‘100’ occures in the column).

Reply | Quote

Replies

You could store the array in a range on a worksheet (which could be hidden) and use Excel worksheetfunctions to perform the count.

If you want to avoid that, you’d have to loop through the array.

Reply | Quote

Here’s what I am trying to achieve:

1. Got a large range with variable number of rows and occasionally changing number of columns
2. Filling it into a two-dimensional ‘MasterArray’ (xRows, yCols)
3. Looping through the ‘MasterArray’ identifying a subset of records – when found added to an Array ‘Tmp1’ (yCols_T1, xRows_T1) (Here i now know the # of columns – # rows are still unknown, thus array must be transposed to have Redim Preserve)
4. Temp Array is ‘re-transposed’ to a final sub-set ‘Subset1(xRows_T1, yCols_T1)’

This snippet actually does the trick, but I’m wondering if it can’t be done in a more efficient way…?

‘Loop Sales force
For c = LBound(arrSlsInfo, 1) To UBound(arrSlsInfo, 1)
j = 1
‘Loop array of companies found for each Sales Person
For i = LBound(arrExtrMarketAll) To UBound(arrExtrMarketAll)
‘If company is covered by sales rep considered – build transposed arrTemp
If arrSlsInfo(c, 3) = arrExtrMarketAll(i, 9) Then
ReDim Preserve arrTemp(UBound(arrExtrMarketAll, 2), j)
For f = 1 To UBound(arrExtrMarketAll, 2)
arrTemp(f, j) = arrExtrMarketAll(i, f)
Next f
j = j + 1
End If
Next i

‘Re-transpose arrTemp – to get “correctly” oriented subset
ReDim arrCosCovered(LBound(arrTemp, 2) To UBound(arrTemp, 2), LBound(arrTemp, 1) To UBound(arrTemp, 1))
For d = LBound(arrTemp, 2) To UBound(arrTemp, 2)
For e = LBound(arrTemp, 1) To UBound(arrTemp, 1)
arrCosCovered(d, e) = arrTemp(e, d)
Next e
Next d
Next c

Question:
Do I need to loop the temporary array to fill ‘Subset1’ or are there other more efficient ways of achieving the above ??
What is the easiest way of doing this kind of extraction from an array into a subarray and is it somehow possible to do this without having to put the values into a worksheet as an intermediary step?

…from here I can move on filling in my worksheet etc. as required.

Reply | Quote

It sounds to me as though you should be using worksheets and filters, or a database!

Reply | Quote