Hi Colin,

Am Tue, 10 Jun 2014 14:17:07 +0100 schrieb Colin Hayes:

The worksheet may have many columns. All columns and their content need
to be present after running the code.

The 'Quantity' column would have been updated to reflect the amount of
duplicates found in the other selected column.

Rows with duplicates would be deleted or hidden.

when is a row a duplicate? If all columns are equal or if only "Ref" is
equal?

Your table in sheet1. Then try foolwong code. It copies the table to
sheet2 and let remain the first occurance of "Ref". All other values
will be deleted. And in the Quantity column there are the sums of the
"Refs"
If that is not what you want please send me a workbook with data and
with example how the result should be.

Sub Test()
Dim ColRef As Long
Dim ColQ As Long
Dim LRow As Long

Sheets("Sheet1").UsedRange.Copy _
Sheets("Sheet2").Range("A1")

With Sheets("Sheet2")
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
ColRef = WorksheetFunction.Match("Ref", .Range("1:1"), 0)
ColQ = WorksheetFunction.Match("Quantity", .Range("1:1"), 0)
.Range(.Cells(2, ColRef), .Cells(LRow, ColRef)).Formula = _
"=IF(CountIf(Sheet1!$G$2:G2,Sheet1!G2)=1,Sheet1!G2 ,"""")"
.Range(.Cells(2, ColQ), .Cells(LRow, ColQ)).Formula = _
"=IF(G2="""","""",SumIf(Sheet1!G:G,G2,Sheet1!D:D)) "
End With

End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional