Highlighting Duplicates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi - I Hope that somebody may be able to help me

I need to highlight duplicate rows in a spreadsheet using
vba and to display a message box indicating that there are duplicated rows
and that a reveiw is required prior to import

The spreadsheet is called Data_01.xls
The potentially duplicated rows relate to columns named
OrderNo : ProductNo : Qty Company

Each row contains an order number with a product number and a quantity

The duplicate rows are usually products ordered twice, not always with the
same quantities, against the same order number

I would be grateful for any help


Sam
 
Sam,

Assuming your values of interest are in columns A and B, with headers in
row1, then the macro below will show any duplicates.

HTH,
Bernie
MS Excel MVP

Sub ShowSamHisDuplicates()
Application.ScreenUpdating = False
Range("A:B").EntireColumn.Insert
Range("A1").Value = "Key"
Range("B1").Value = "Count"
Range("A2").FormulaR1C1 = "=RC[2]&RC[3]"
Range("B2").FormulaR1C1 = "=COUNTIF(C[-1],RC[-1])"
Range("A2:B2").AutoFill _
Destination:=Range("A2:B" & Range("C65536").End(xlUp).Row)
Range("B:B").AutoFilter Field:=1, Criteria1:=">1"
If Range("B1").CurrentRegion.Columns(2).SpecialCells _
(xlCellTypeVisible).Cells.Count > 1 Then
Application.ScreenUpdating = True
MsgBox "There are duplicated values"
Else
Range("A:B").EntireColumn.Delete
Application.ScreenUpdating = True
MsgBox "There were no duplicated values"
End If
End Sub
 
Sam wrote:
|| Hi - I Hope that somebody may be able to help me
||
|| I need to highlight duplicate rows in a spreadsheet using
|| vba and to display a message box indicating that there are
|| duplicated rows and that a reveiw is required prior to import
||

If you use Conditional Formatting, it will actually mark which rows are
duplicates.....
Have a look here:
http://www.cpearson.com/excel/duplicat.htm#HighlightingDuplicates
 
Gordon,

The CF solution won't work on two columns - it would require an additional
column of formulas to tie in the CF formula.

Bernie
MS Excel MVP
 
Bernie Deitrick wrote:
|| Gordon,
||
|| The CF solution won't work on two columns - it would require an
|| additional column of formulas to tie in the CF formula.
||
|| Bernie
|| MS Excel MVP

In that case, why doesn't the OP just sort by Order number followed by Part
Number? The duplicates will stick out.
 
Bernie,thanks for your response
The duplicated rows are in columns A that holds the orderNo and P productNo

Each row is for a product order and the identifier to tie the order together
is the orderNo. The order number can be duplicated but they shouldn't have
the same product for the same order.
My problem is that the order spreadheet can contain up to 400 lines and
sorting manually and de-duping takes me ages.

I tried your solution after changing the column references but it still left
me with dupes.

I am sorry if I am taking up too much of your time

Thanks

Sam

Bernie Deitrick said:
Sam,

Assuming your values of interest are in columns A and B, with headers in
row1, then the macro below will show any duplicates.

HTH,
Bernie
MS Excel MVP

Sub ShowSamHisDuplicates()
Application.ScreenUpdating = False
Range("A:B").EntireColumn.Insert
Range("A1").Value = "Key"
Range("B1").Value = "Count"
Range("A2").FormulaR1C1 = "=RC[2]&RC[3]"
Range("B2").FormulaR1C1 = "=COUNTIF(C[-1],RC[-1])"
Range("A2:B2").AutoFill _
Destination:=Range("A2:B" & Range("C65536").End(xlUp).Row)
Range("B:B").AutoFilter Field:=1, Criteria1:=">1"
If Range("B1").CurrentRegion.Columns(2).SpecialCells _
(xlCellTypeVisible).Cells.Count > 1 Then
Application.ScreenUpdating = True
MsgBox "There are duplicated values"
Else
Range("A:B").EntireColumn.Delete
Application.ScreenUpdating = True
MsgBox "There were no duplicated values"
End If
End Sub



Sam said:
Hi - I Hope that somebody may be able to help me

I need to highlight duplicate rows in a spreadsheet using
vba and to display a message box indicating that there are duplicated rows
and that a reveiw is required prior to import

The spreadsheet is called Data_01.xls
The potentially duplicated rows relate to columns named
OrderNo : ProductNo : Qty Company

Each row contains an order number with a product number and a quantity

The duplicate rows are usually products ordered twice, not always with the
same quantities, against the same order number

I would be grateful for any help


Sam
Click to expand...
Click to expand...
 
Sam,

The only change needed would be

From:
Range("A2").FormulaR1C1 = "=RC[2]&RC[3]"

To:
Range("A2").FormulaR1C1 = "=RC[2]&RC[17]"

That would look for items with the same values in columns A and P.

HTH,
Bernie
MS Excel MVP

Sam said:
Bernie,thanks for your response
The duplicated rows are in columns A that holds the orderNo and P productNo

Each row is for a product order and the identifier to tie the order together
is the orderNo. The order number can be duplicated but they shouldn't have
the same product for the same order.
My problem is that the order spreadheet can contain up to 400 lines and
sorting manually and de-duping takes me ages.

I tried your solution after changing the column references but it still left
me with dupes.

I am sorry if I am taking up too much of your time

Thanks

Sam

Bernie Deitrick said:
Sam,

Assuming your values of interest are in columns A and B, with headers in
row1, then the macro below will show any duplicates.

HTH,
Bernie
MS Excel MVP

Sub ShowSamHisDuplicates()
Application.ScreenUpdating = False
Range("A:B").EntireColumn.Insert
Range("A1").Value = "Key"
Range("B1").Value = "Count"
Range("A2").FormulaR1C1 = "=RC[2]&RC[3]"
Range("B2").FormulaR1C1 = "=COUNTIF(C[-1],RC[-1])"
Range("A2:B2").AutoFill _
Destination:=Range("A2:B" & Range("C65536").End(xlUp).Row)
Range("B:B").AutoFilter Field:=1, Criteria1:=">1"
If Range("B1").CurrentRegion.Columns(2).SpecialCells _
(xlCellTypeVisible).Cells.Count > 1 Then
Application.ScreenUpdating = True
MsgBox "There are duplicated values"
Else
Range("A:B").EntireColumn.Delete
Application.ScreenUpdating = True
MsgBox "There were no duplicated values"
End If
End Sub



Sam said:
Hi - I Hope that somebody may be able to help me

I need to highlight duplicate rows in a spreadsheet using
vba and to display a message box indicating that there are duplicated rows
and that a reveiw is required prior to import

The spreadsheet is called Data_01.xls
The potentially duplicated rows relate to columns named
OrderNo : ProductNo : Qty Company

Each row contains an order number with a product number and a quantity

The duplicate rows are usually products ordered twice, not always with the
same quantities, against the same order number

I would be grateful for any help


Sam
Click to expand...
Click to expand...
Click to expand...
 
Back
Top