How to determine Data sequence and Frequency

[grudum]

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..

[6StringJazzer]

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.

[6StringJazzer]

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.

[grudum]

6 String, Thank you for your reply.

I think you have the right idea...the trouble some part is exactly as you describe. The top row relates only to the results for row 1. Being able to track the order is paramount.

The values can bounce around, but will always be 1-9.

Im looking into writing a User defined function, but its a challange as I havnt written very much VB.

The function needs to select a range, then cycle through the cells in that range and check if cell1 = cell2, cell2=cell3 untill the end of the range, if the cells are not equal, then Concatonate the value of the not equal cell in order.

I really have no idea how to write that in VB as a function, but I think thats what needs to happen. Once I have that, I can splice the countif results in and then my problem will be solved..

Does that make sense?

Any ideas?

[grudum]

That output is perfect! Thank you kind sir.

What do I need to do to make this work in my 'real' workbook?

[6StringJazzer]

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.

Public Sub SummarizeSequences()
   Dim CurRow As Long ' current row being summarized
   Dim LastCol As Long ' last column of a row
   Dim SumCol As Long ' column with summary data
   Dim ValCount As Long ' current count of value found
   Dim ValFound As Long
 
   Dim i As Long ' loop counter
 
   CurRow = 1
   Do While Cells(CurRow, 1) <> ""
 
      ' Summarize a row
 
      ' Insert a row for the summary heading
      Cells(CurRow, 1).EntireRow.Insert
      CurRow = CurRow + 1
 
      ' Find last used column
      LastCol = Cells(CurRow, 1).End(xlToRight).Column
 
      SumCol = LastCol + 2
      ValFound = Cells(CurRow, 1)
      ValCount = 1
      Cells(CurRow - 1, SumCol) = ValFound
 
      For i = 2 To LastCol
         If Cells(CurRow, i) = ValFound Then
            ValCount = ValCount + 1
         Else
            Cells(CurRow, SumCol) = ValCount
 
            SumCol = SumCol + 1
            ValFound = Cells(CurRow, i)
            ValCount = 1
            Cells(CurRow - 1, SumCol) = ValFound
         End If
      Next i
      Cells(CurRow, SumCol) = ValCount
 
      CurRow = CurRow + 1
   Loop
End Sub

[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?

[6StringJazzer]

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...

[6StringJazzer]

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.

[grudum]

It seems to time out after 3 rows of data..

[grudum]

I'm using this on 2007 and it works perfectly for the first three rows of data, then I get a Runtime error: 13 mismatch type . Any ideas?

[grudum]

Figured it out...I had to clear all the #N/A cells. Works flawlessly. Thank you!!!

[6StringJazzer]

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