# Highlighting duplicate column-pair and counting the rows Excel

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.

-
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

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

into this

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