row comparison

  • Thread starter Thread starter graphicsxp
  • Start date Start date
G

graphicsxp

Hi,

I have a spreadsheet that looks similar to the below:

row 1: 1 aaa bbb ccc
row2 : 1 aaa bbb ddd
row3 : 2 eee fff ggg
row4 : 2 eee zzz yyy
..
..
..

What I want is a macro to run through all the rows and do a row-to-row
comparison between rows with an identical id (first column).

Differences should be highlighted by changing the cell colour to red.
For example, with the above, a comparison of the first two rows should
highlight the cells 'ccc' and 'ddd'. A comparison of the next two rows
will highlight 'fff', 'zzz', 'ggg' and 'yyy'.

Could somebody show me how to achieve this ?

Thanks
 
Hello,

Instead of using a macro, you can use conditionnal formating.

I assumed your data are in rows 1 to 4 and in columns A to D.

Select the cell B1 and apply this conditionnal formating
(as a formula):
=SUMPRODUCT(--($A$1:$A$4=$A1) )<>SUMPRODUCT(--(B$1:B$4=B1))
Choose Red as the cell colour.

Then copy the cell B1 and copy its format (paste special - format)
to the others cells (ie C1:D1 and B2:D4)

NB: This formula will compare all the lines with the same key. If there
are e.g. four lines with the same key, the formula will compare the
four lines and will highlight the four cells in a column even if only
one value is not equal to the three others same values.
 
Hello,

Instead of using a macro, you can use conditionnal formating.

I assumed your data are in rows 1 to 4 and in columns A to D.

Select the cell B1 and apply this conditionnal formating
(as a formula):
=SUMPRODUCT(--($A$1:$A$4=$A1) )<>SUMPRODUCT(--(B$1:B$4=B1))
Choose Red as the cell colour.

Then copy the cell B1 and copy its format (paste special - format)
to the others cells (ie C1:D1 and B2:D4)

NB: This formula will compare all the lines with the same key. If there
are e.g. four lines with the same key, the formula will compare the
four lines and will highlight the four cells in a column even if only
one value is not equal to the three others same values.
Click to expand...

Hi Char,

Thank you for your support. What I need really is a macro as there
could be thousands of records in my spreadsheet.

Let me explain again:

Let's focus on the first two rows. You'll notice that they have the
same id (first column) : value=1

The next two rows have also the same id (value=2).

In a real case scenario I could have up to 65,000 rows and they will
all follow the same pattern (the id changes every two rows) .

Let's get back to our first two rows. In the example I gave, COL1 has
value 't' for both rows, so we are happy because it's the same value.
However, COL2 has value 's' for row 1, and value 'z' for row 2.
Therefore we want to highlight these cells in red to make it clear
that there is a difference between row 1 and row 2 for this particular
column.

Is this clearer ?
 
Hello,

I hope I have understand what you want.
I assumed the keys of your records are in column A
I assumed the fields to compare are in columns B,C,D
for every two rows of records.
I assumed that columns A to D contains your data and
only your data.

Then try this macro:
Sub test()

Dim LastRow As Long, I As Long, J As Integer

LastRow = Cells.Rows.Count
LastRow = Cells(LastRow, 1).End(xlUp).Row

Range(Cells(1, 1), Cells(LastRow, 4)). _
Interior.ColorIndex = xlColorIndexAutomatic

Application.ScreenUpdating = False

For I = LastRow To 2 Step -2
If Cells(I, 1) = Cells(I - 1, 1) Then
For J = 2 To 4
If Cells(I, J) <> Cells(I - 1, J) Then
Cells(I, J).Interior.ColorIndex = 3
Cells(I - 1, J).Interior.ColorIndex = 3
End If
Next J
End If
Next I

Application.ScreenUpdating = True
End Sub





Hi Char,

Thank you for your support. What I need really is a macro as there
could be thousands of records in my spreadsheet.

Let me explain again:

Let's focus on the first two rows. You'll notice that they have the
same id (first column) : value=1

The next two rows have also the same id (value=2).

In a real case scenario I could have up to 65,000 rows and they will
all follow the same pattern (the id changes every two rows) .

Let's get back to our first two rows. In the example I gave, COL1 has
value 't' for both rows, so we are happy because it's the same value.
However, COL2 has value 's' for row 1, and value 'z' for row 2.
Therefore we want to highlight these cells in red to make it clear
that there is a difference between row 1 and row 2 for this particular
column.

Is this clearer ?
 
Hello,

I hope I have understand what you want.
I assumed the keys of your records are in column A
I assumed the fields to compare are in columns B,C,D
for every two rows of records.
I assumed that columns A to D contains your data and
only your data.

Then try this macro:
Sub test()

Dim LastRow As Long, I As Long, J As Integer

LastRow = Cells.Rows.Count
LastRow = Cells(LastRow, 1).End(xlUp).Row

Range(Cells(1, 1), Cells(LastRow, 4)). _
Interior.ColorIndex = xlColorIndexAutomatic

Application.ScreenUpdating = False

For I = LastRow To 2 Step -2
 If Cells(I, 1) = Cells(I - 1, 1) Then
  For J = 2 To 4
  If Cells(I, J) <> Cells(I - 1, J) Then
   Cells(I, J).Interior.ColorIndex = 3
   Cells(I - 1, J).Interior.ColorIndex = 3
  End If
  Next J
 End If
Next I

Application.ScreenUpdating = True
End Sub

Hi Char,

Thank you for your support. What I need really is a macro as there
could be thousands of records in my spreadsheet.

Let me explain again:

Let's focus on the first two rows. You'll notice that they have the
same id (first column) : value=1

The next two rows have also the same id (value=2).

In a real case scenario I could have up to 65,000 rows and they will
all follow the same pattern (the id changes every two rows) .

Let's get back to our first two rows. In the example I gave, COL1 has
value 't' for both rows, so we are happy because it's the same value.
However, COL2 has value 's' for row 1, and value 'z' for row 2.
Therefore we want to highlight these cells in red to make it clear
that there is a difference between row 1 and row 2 for this particular
column.

Is this clearer ?
Click to expand...

Thanks .

You understood right :)

Your macro works but I found a faster way :

Sub snb()
Set sq = Cells(1).CurrentRegion

For Each rw In sq.Rows
If rw.Row Mod 2 = 0 Then
c01 = ""
For Each cl In rw.Columns
If not cl = cl.Offset(1) Then c01 = c01 & "," &
cl.Offset(1).Address
Next
Range(Mid(c01, 2)).Interior.ColorIndex = 6
End If
Next
End Sub

thanks for helping
 
OK It works by me too.

By me (excel2003), if two lines are identical, the macro will fail because
Range(Mid(c01, 2)) is not defined (c01 is empty)

"graphicsxp" <[email protected]> a écrit dans le message de
Hello,

I hope I have understand what you want.
I assumed the keys of your records are in column A
I assumed the fields to compare are in columns B,C,D
for every two rows of records.
I assumed that columns A to D contains your data and
only your data.

Then try this macro:
Sub test()

Dim LastRow As Long, I As Long, J As Integer

LastRow = Cells.Rows.Count
LastRow = Cells(LastRow, 1).End(xlUp).Row

Range(Cells(1, 1), Cells(LastRow, 4)). _
Interior.ColorIndex = xlColorIndexAutomatic

Application.ScreenUpdating = False

For I = LastRow To 2 Step -2
If Cells(I, 1) = Cells(I - 1, 1) Then
For J = 2 To 4
If Cells(I, J) <> Cells(I - 1, J) Then
Cells(I, J).Interior.ColorIndex = 3
Cells(I - 1, J).Interior.ColorIndex = 3
End If
Next J
End If
Next I

Application.ScreenUpdating = True
End Sub

Hi Char,

Thank you for your support. What I need really is a macro as there
could be thousands of records in my spreadsheet.

Let me explain again:

Let's focus on the first two rows. You'll notice that they have the
same id (first column) : value=1

The next two rows have also the same id (value=2).

In a real case scenario I could have up to 65,000 rows and they will
all follow the same pattern (the id changes every two rows) .

Let's get back to our first two rows. In the example I gave, COL1 has
value 't' for both rows, so we are happy because it's the same value.
However, COL2 has value 's' for row 1, and value 'z' for row 2.
Therefore we want to highlight these cells in red to make it clear
that there is a difference between row 1 and row 2 for this particular
column.

Is this clearer ?
Click to expand...

Thanks .

You understood right :)

Your macro works but I found a faster way :

Sub snb()
Set sq = Cells(1).CurrentRegion

For Each rw In sq.Rows
If rw.Row Mod 2 = 0 Then
c01 = ""
For Each cl In rw.Columns
If not cl = cl.Offset(1) Then c01 = c01 & "," &
cl.Offset(1).Address
Next
Range(Mid(c01, 2)).Interior.ColorIndex = 6
End If
Next
End Sub

thanks for helping
 
Back
Top