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 in message
...
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 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 he
http://www.cpearson.com/excel/duplic...tingDuplicates
--
Interim Systems and Management Accounting
Gordon Burgess-Parker
Director
www.gbpcomputing.co.uk

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

"Gordon" wrote in message
...
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 he
http://www.cpearson.com/excel/duplic...tingDuplicates
--
Interim Systems and Management Accounting
Gordon Burgess-Parker
Director
www.gbpcomputing.co.uk

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.


--
Interim Systems and Management Accounting
Gordon Burgess-Parker
Director
www.gbpcomputing.co.uk

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" wrote:

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 in message
...
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,

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" wrote in message
...
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" wrote:

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 in message
...
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