Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Given the data below, the column-pair with the same values for at least 4 consecutive rows should be highlighted.

Here's an image for better visualization:

2   2
3   4
3   4
3   4
3   4
2   3
1   2
2   2
3   3
3   3
3   3
3   3
2   3
2   3
2   3
2   3
2   2
3   4
3   4
3   4
3   4
3   4

The output should be something like this, where the column-pair values that are the same for at least 4 consecutive rows are highlighted:

2   2
3   4
3   4
3   4
3   4
2   3
1   2
2   2
3   3
3   3
3   3
3   3
2   3
2   3
2   3
2   3
2   2
3   4
3   4
3   4
3   4
3   4

Then, I need to know the number of instances of the N-consecutive equal column-pair. Considering the data above, N=4 should be 3 and N=5 should be 1, where N is the number of rows that the column-pair is consecutively equal.

share|improve this question
1  
As-is, this isn't really a question. What have you tried already, and where are you getting stuck? Please edit your question to include that information. –  Ƭᴇcʜιᴇ007 Oct 14 '12 at 15:49
    
Ah, this one looked a lot like your previous question. It's a little different though. I've decided to keep it open, but please include the information about what you already have. –  slhck Oct 14 '12 at 15:53
    
This sounds a little complicated to just use conditional formatting, my suspicion is that it needs a proper macro written to do it well. As techie says, what have you tried so far? –  Mokubai Oct 14 '12 at 16:36

1 Answer 1

As I mentioned in my comment I think this is a bit beyond what conditional formatting is capable of.

Therefore I submit for your perusal the following Excel Macro that turns this

Before

into this

After

I accept no responsibility for your data with this. It will need fine tuning to your circumstances (row and column start numbers, etc.) and if it creates a black hole and your computer vanishes in a puff of something then all I would say is "Wow, did you get that on camera?" Because that would have been awesome.

That said, it worked for me and if you set it up right it could work for you too. Or you can modify it to better suit your needs.

Sub RepeatedRows()
'Written by Mokubai, 14/10/12
FirstColumn = "A"
LastColumn = "B"
StartRow = 2

' First we establish how far we are going down the table
NumRows = ActiveSheet.UsedRange.Rows.Count
NumCols = ActiveSheet.UsedRange.Columns.Count

'Then we start working out
RowString = ""
oldRowString = ""
numRepeated = 0

For i = StartRow To NumRows
    FirstCell = FirstColumn & i
    'FirstCell = "A1"
    LastCell = LastColumn & i
    'Create a temporary string that is the row contents
    For Each Cell In Range(FirstCell, LastCell)
        RowString = RowString & Cell.Value
    Next Cell
    'compare it to the previous row:
    If RowString = oldRowString Then
        numInGroup = numInGroup + 1
    Else
        numInGroup = 0
        Range(FirstCell).EntireRow.Interior.ColorIndex = 0
    End If

    'now we fill in the current and all previous rows to the same colour
    'I have done absolutely nothing facy with the colouring, it is simply using the value
    For col = 0 To numInGroup
        CellNo = FirstColumn & (i - col)
        Range(CellNo).EntireRow.Interior.ColorIndex = (numInGroup)

        'this next bit is so that only the last member of the group gets a value in it
        'in order to help define where the groups are
        EmptyColumn = "C" & i - col
        Range(EmptyColumn).Select
        If col = 0 Then
            ActiveCell.Value = numInGroup + 1
        Else
            ActiveCell.Value = ""
        End If
    Next col

    'in either case we store the current row string and clear the temporary row string and repeat
    oldRowString = RowString
    RowString = ""
Next

End Sub
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.