ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   New Users to Excel (https://www.excelbanter.com/new-users-excel/)
-   -   Highlighting Duplicates (https://www.excelbanter.com/new-users-excel/18225-highlighting-duplicates.html)

Sam

Highlighting Duplicates
 
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




Bernie Deitrick

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






Gordon

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

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





Gordon

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



Sam

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







Bernie Deitrick

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










All times are GMT +1. The time now is 10:48 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com