How can Duplicate rows in a range be compared and higlighted?

A

Andybooth

New Member
Joined
Jun 12, 2013
Messages
1
I am trying to step down row by row through a range and compare the current row against all the other rows in a range before and after the current row.
If a possible duplicate is found where 8 of 9 columns in the row are a duplicate of other rows I want to highlight the rows for investigation.
I have tried to do this with code, but I am limited in that I am only able to step down through a column and compare the cells for duplicates
and then highlight them. This code I have attached below.
I also incude an example of the data I am comparing and two rows higlighted were duplicates have been found with the only difference being found is the REFNO which I expect, as I am trying to compare the underlying transaction details.
Can anyone assist me with this as I am stuck on how to store the row for comparison and then go through each of the other rows and compare against the current row.
Thanks
Andy


Code: 
Sub FindDups()
    ScreenUpdating = False
   FirstItem = ActiveCell.Value
   SecondItem = ActiveCell.Offset(1, 0).Value
   Offsetcount = 1
   Do While ActiveCell <> ""
      If FirstItem = SecondItem Then
        ActiveCell.Offset(Offsetcount, 0).Interior.Color = RGB(255, 0, 0)
        Offsetcount = Offsetcount + 1
        SecondItem = ActiveCell.Offset(Offsetcount, 0).Value
      Else
        ActiveCell.Offset(Offsetcount, 0).Select
        FirstItem = ActiveCell.Value
        SecondItem = ActiveCell.Offset(1, 0).Value
        Offsetcount = 1
      End If
   Loop
   ScreenUpdating = True
End Sub

[TABLE="width: 367"]
<COLGROUP><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1572" width=43><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 2011" width=55><COL style="WIDTH: 25pt; mso-width-source: userset; mso-width-alt: 1206" width=33><COL style="WIDTH: 27pt; mso-width-source: userset; mso-width-alt: 1316" width=36><COL style="WIDTH: 22pt; mso-width-source: userset; mso-width-alt: 1060" width=29><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" span=2 width=65><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3474" width=95><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><TBODY>[TR]
[TD="class: xl66, width: 43, bgcolor: transparent"]REFNO[/TD]
[TD="class: xl66, width: 55, bgcolor: transparent"]PRODUCT[/TD]
[TD="class: xl66, width: 33, bgcolor: transparent"]TYPE[/TD]
[TD="class: xl66, width: 36, bgcolor: transparent"]Buyer[/TD]
[TD="class: xl66, width: 29, bgcolor: transparent"]CCY[/TD]
[TD="class: xl66, width: 65, bgcolor: transparent"]AMOUNT[/TD]
[TD="class: xl66, width: 65, bgcolor: transparent"]Interest rate[/TD]
[TD="class: xl66, width: 95, bgcolor: transparent"]Transaction DATE[/TD]
[TD="class: xl66, width: 68, bgcolor: transparent"]DeliveryDate[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 43, bgcolor: #d9d9d9"]123456[/TD]
[TD="class: xl71, width: 55, bgcolor: #d9d9d9"]XXXX[/TD]
[TD="class: xl71, width: 33, bgcolor: #d9d9d9"]T[/TD]
[TD="class: xl71, width: 36, bgcolor: #d9d9d9"]ME[/TD]
[TD="class: xl71, width: 29, bgcolor: #d9d9d9"]EUR[/TD]
[TD="class: xl72, width: 65, bgcolor: #d9d9d9"]1,233.58[/TD]
[TD="class: xl73, width: 65, bgcolor: #d9d9d9"].00000000[/TD]
[TD="class: xl71, width: 95, bgcolor: #d9d9d9"]06 Jun 2013[/TD]
[TD="class: xl71, width: 68, bgcolor: #d9d9d9"]07 Jun 2013[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 43, bgcolor: transparent"]123457[/TD]
[TD="class: xl67, width: 55, bgcolor: transparent"]XXXX[/TD]
[TD="class: xl67, width: 33, bgcolor: transparent"]T[/TD]
[TD="class: xl67, width: 36, bgcolor: transparent"]US[/TD]
[TD="class: xl67, width: 29, bgcolor: transparent"]EUR[/TD]
[TD="class: xl68, width: 65, bgcolor: transparent"]1,523.35[/TD]
[TD="class: xl69, width: 65, bgcolor: transparent"].07000000[/TD]
[TD="class: xl67, width: 95, bgcolor: transparent"]06 Jun 2013[/TD]
[TD="class: xl70, width: 68, bgcolor: transparent"]07-Aug-13[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 43, bgcolor: transparent"]123897[/TD]
[TD="class: xl67, width: 55, bgcolor: transparent"]XXXX[/TD]
[TD="class: xl67, width: 33, bgcolor: transparent"]T[/TD]
[TD="class: xl67, width: 36, bgcolor: transparent"]US[/TD]
[TD="class: xl67, width: 29, bgcolor: transparent"]USD[/TD]
[TD="class: xl68, width: 65, bgcolor: transparent"]894,564.25[/TD]
[TD="class: xl69, width: 65, bgcolor: transparent"].07000000[/TD]
[TD="class: xl67, width: 95, bgcolor: transparent"]06 Jun 2013[/TD]
[TD="class: xl70, width: 68, bgcolor: transparent"]15-Oct-13[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 43, bgcolor: #d9d9d9"]123576[/TD]
[TD="class: xl71, width: 55, bgcolor: #d9d9d9"]XXXX[/TD]
[TD="class: xl71, width: 33, bgcolor: #d9d9d9"]T[/TD]
[TD="class: xl71, width: 36, bgcolor: #d9d9d9"]ME[/TD]
[TD="class: xl71, width: 29, bgcolor: #d9d9d9"]EUR[/TD]
[TD="class: xl72, width: 65, bgcolor: #d9d9d9"]1,233.58[/TD]
[TD="class: xl73, width: 65, bgcolor: #d9d9d9"].00000000[/TD]
[TD="class: xl71, width: 95, bgcolor: #d9d9d9"]06 Jun 2013[/TD]
[TD="class: xl71, width: 68, bgcolor: #d9d9d9"]07 Jun 2013[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 43, bgcolor: transparent"]124987[/TD]
[TD="class: xl67, width: 55, bgcolor: transparent"]XXXX[/TD]
[TD="class: xl67, width: 33, bgcolor: transparent"]O[/TD]
[TD="class: xl67, width: 36, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 29, bgcolor: transparent"]<st1:stockticker>GBP</st1:stockticker> [/TD]
[TD="class: xl68, width: 65, bgcolor: transparent"]18,711.00[/TD]
[TD="class: xl69, width: 65, bgcolor: transparent"].30000000[/TD]
[TD="class: xl67, width: 95, bgcolor: transparent"]06 Jun 2013[/TD]
[TD="class: xl70, width: 68, bgcolor: transparent"]30-Sep-13[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 43, bgcolor: transparent"]156912[/TD]
[TD="class: xl67, width: 55, bgcolor: transparent"]XXXX[/TD]
[TD="class: xl67, width: 33, bgcolor: transparent"]O[/TD]
[TD="class: xl67, width: 36, bgcolor: transparent"]WE[/TD]
[TD="class: xl67, width: 29, bgcolor: transparent"]USD[/TD]
[TD="class: xl68, width: 65, bgcolor: transparent"]-184,768.47[/TD]
[TD="class: xl69, width: 65, bgcolor: transparent"].15000000[/TD]
[TD="class: xl67, width: 95, bgcolor: transparent"]06 Jun 2013[/TD]
[TD="class: xl67, width: 68, bgcolor: transparent"]07 Jun 2013[/TD]
[/TR]
</TBODY>[/TABLE]
 
You must log in or register to reply here.

Similar threads

H
  • Question Question
Macros - Auto Copy paste row when cell value changes
Replies
7
Views
6K
Fluff
Fluff
N
  • Question Question
excel macro to split data into multiple worksheets based on conditions worksheet
Replies
4
Views
4K
Fluff
Fluff
S
  • Question Question
Locating duplicate rows in Excel
Replies
1
Views
594
JoeMo
J
G
  • Question Question
Transpose matching data from columns to rows
Replies
3
Views
877
bwaaack
B
P
  • Question Question
Removing duplicate and original entries from a string within a single cell
Replies
3
Views
782
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,226,867
Messages
6,193,430
Members
453,799
Latest member
shanley ducker

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top