Counting instances of contigous numbers in a column

[ChrisFlash36]

Hi,

I apologise if my attempt at a descriptive subject is less clear than "HELP!!!!". I'll try again:

I have a column containing a discrete random variable, forced to appear in sets of disrete random lengths. E.g.

1
1
1
0.5
0.5
0.5
0.5
0.5
0.5
3
3
3
...and so on

I want to count the instances of each set length for each number. Or, put in another way, produce a table with possible set lengths as the column headings and possible numbers as the row titles (or vice versa).

I have no idea how to do this in VBA. Can anyone help?

(Fairly new to VBA)

Thanks

 

[SkipVought]

Hi,

Suppose you post a compact example of the source data AND the result that you expect, based on the example source.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Oh how about using a PivotTable?

Your example...
[tt]
Source
1
1
1
0.5
0.5
0.5
0.5
0.5
0.5
3
3
3
[/tt]
result
[tt]
Count of Source
Source Total
0.5 6
1 3
3 3

[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ChrisFlash36]

Thanks for replying SkipVought. I tried to create a box.net account but the sign-up kept erroring. Can you recommend an alternative or can I email a file to you?

Incase not, here is some clarification:
In column A, discrete data is being generated randomly within a particular distribution with each 'Application.Calculate'.

With each re-calculation, I want to know how many times "1" is generated only once, how many times "1" is generated twice consecutively, how many times "1" is generated three times consecutively, and how many times "1" is generated four times consecutively. For the sake of discussion, I will call the once, twice, three times and four times 'persistences'.

Then the same is required for each possible discrete number generated.

So I want the results for each recalculation to populate a row, therefore there will be as many rows as there are recalculations.

Ultimately, I need to know the average frequency of each persistence for each discrete random number. I'm sure there is a much more efficient way to do this than populating row after row with data, but I've set up the average frequencies of the discrete numbers themselves in this way so I know how to do this bit. The bit that I have no idea how to do is to recognise an instance of n contigous cells containing the same number in a column of randomly generated numbers.

I hope this makes some sense...

 

[SkipVought]

EXAMPLE!!!

VALUES!!!!

Expected results!!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Assume this distribution
[tt]
Source Grp
1 1
1 1
1 1
0.5 2
0.5 2
0.5 2
0.5 2
0.5 2
0.5 2
3 3
3 3
3 3
1 4
1 4
0.5 5
0.5 5
0.5 5
3 6
1 7
[/tt]
Where Grp is calculated...
[tt
B2: =IF(A2=A1,B1,IF(ISNUMBER(B1),B1,0)+1)
[/tt]
The PivotTable result...
[tt]
Count of Grp
Source Grp Total
0.5 2 6
5 3
1 1 3
4 2
7 1
3 3 3
6 1
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}