Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding Duplicates | Excel Worksheet Functions | |||
Finding and Deleting duplicates in a column | Excel Worksheet Functions | |||
Finding Duplicates and somehow flagging them in another column | Excel Discussion (Misc queries) | |||
removing duplicates testing in 2 coloms | Excel Worksheet Functions | |||
Find duplicates | Excel Discussion (Misc queries) |