Hi,
I have a problem that i'm trying to solve but don't even know which excel functions i would use to solve it. I usually can do what i need in Excel then can just work out some VBA to automate it, this time i don't really know where to start. So any pointers would be welcome.
The problem:
I have columns of data, from hundreds of rows up to potentially 20 thousand rows. The data is quite simple usually, taking the form of 0, 1, 2, 3. I would like to write some VBA code to count the consecutive values of 1,2 and 3. And return the maximum amount of consecutive rows for each value.
Eg: in the set of values below (They're in a single row below to save space but in the worksheet would be in a single column):
2, 1, 1, 1, 2, 3, 3, 3, 3, 2, 2, 3 3, 1.
The code should return the result:
1 = 3
2 = 2
3 = 4
To complicate it further as the amount of data is large, i don't want a single row or two ruining a good run of consecutive equal values. So was thinking of how to get a 95% of values are equal within a set amount of rows, eg: if 95% of the values within say 30 rows are equal to 3, then return true. The code would then have to iterate through the rows examining the next 30 rows, which could be slow.
Any ideas?
Feel free to ask if i didn't describe the problem adequately.
I have a problem that i'm trying to solve but don't even know which excel functions i would use to solve it. I usually can do what i need in Excel then can just work out some VBA to automate it, this time i don't really know where to start. So any pointers would be welcome.
The problem:
I have columns of data, from hundreds of rows up to potentially 20 thousand rows. The data is quite simple usually, taking the form of 0, 1, 2, 3. I would like to write some VBA code to count the consecutive values of 1,2 and 3. And return the maximum amount of consecutive rows for each value.
Eg: in the set of values below (They're in a single row below to save space but in the worksheet would be in a single column):
2, 1, 1, 1, 2, 3, 3, 3, 3, 2, 2, 3 3, 1.
The code should return the result:
1 = 3
2 = 2
3 = 4
To complicate it further as the amount of data is large, i don't want a single row or two ruining a good run of consecutive equal values. So was thinking of how to get a 95% of values are equal within a set amount of rows, eg: if 95% of the values within say 30 rows are equal to 3, then return true. The code would then have to iterate through the rows examining the next 30 rows, which could be slow.
Any ideas?
Feel free to ask if i didn't describe the problem adequately.
