How to determine Data sequence and Frequency

I have data that I need to be able to count the number of days a value has been in and also what it moved into next.

For instance, how long was the row in a 0, what came after that, how long was it that value, what came next, how long, and so on...

This is a massive spread sheet and I need to track the delay and movement of these values.

I've already used Countifs for the general counts, but I can't figure out how to combine that with the order of events. I'm looking for a "summary" that I can put off to the right next to this data.

Is this possible to do cleanly?



0 0 0 0 0 0 3 3 3 3 3 3 3 3 1 1 1 1 1 1 1 1 1 1
0 0 0 0 0 0 6 6 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7
0 1 1 1 1 1 1 1 1 1 1 1 1 1 5 5 5 5 5 5 5 5 5 5
0 3 3 3 3 3 3 3 3 3 3 3 3 3 3 1 9 9 9 9 9 9 9 9


Here is an example for the first row of the result that I want:
(I need it do it for all rows though..)
...................................................................0 3 1
0 0 0 0 0 0 3 3 3 3 3 3 3 3 1 1 1 1 1 1 1 1 1 1 | 6 8 10
0 0 0 0 0 0 6 6 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7
0 1 1 1 1 1 1 1 1 1 1 1 1 1 5 5 5 5 5 5 5 5 5 5
0 3 3 3 3 3 3 3 3 3 3 3 3 3 3 1 9 9 9 9 9 9 9 9

Let me know if I need to clarify..

Originally Posted by grudum

For instance, how long was the row in a 0, what came after that, how long was it that value, what came next, how long, and so on...

That "and so on" will kill you

Here is an example for the first row of the result that I want:
(I need it do it for all rows though..)
...................................................................0 3 1
0 0 0 0 0 0 3 3 3 3 3 3 3 3 1 1 1 1 1 1 1 1 1 1 | 6 8 10

That row can be done easily with COUNTIF, once you have the values you are looking for in the top row. Is that the troublesome part--creating that top row? Can the values bounce around so that you might have some 3's, then some 1's, then go back to 3's again--and not just count all the 3's but show there were two blocks of them?

This can be done rather easily using VBA code. I think it might be possible with formulas, but I'm not sure how.

Here is an example using VBA. You must enable macros when opening the attached workbook.

It will open on a page containing data. It uses the same sample data that you show in your post but will work for any number of rows and columns.

Go to the "Controls" tab and press the button. Return to your "Data" tab to see the results.

If all your data is on one worksheet, you could just copy the data right into the workbook I provided.

You can copy the following VBA code into the sheet module for your own data. Then you can either execute in the VBA environment with F5, or you can install a button to run it, like I did.

As I mentioned, this will handle any number of rows and columns. However, note that it creates a new row for each row of data, so the number of rows of data is limited to half the maximum rows that Excel can handle. For 2003 that's 64K but in 2007 it's 1M.

Please Login or Register  to view this content.

Thanks for that, I did not include sufficient error checking.

Originally Posted by grudum

Thank you 6String!

Will it be able to do 1000's of rows like in the attached file? Is it possible to automate highlighting every other row (the results) light grey?

The highlighting isn't difficult but will have to wait until morning...

Here is a tuned up version with the following updates:

Values are shaded, I think this is what you wanted

If an error value is encountered, it is treated as the string "#E" and counted. Therefore if you have 8 #N/A values, you will see a summary of #E with 8 values. The same would be true if you had 4 #N/A values and 4 #VALUE! errors.

When complete, it will automatically activate the sheet with the data.