HI All

Hope someone can help me solve a problem.

I need a small piece of VBA to find duplicate values in a selected
column , and then augment a second selected column by the amount found.
The duplicates themselves could then be deleted or hidden.

For example :

Quantity Ref

1 223
1 217
3 223
1 217
1 625


Would become :

Quantity Ref

4 223
2 217
1 625


Columns without duplicates would of course remain untouched.

Because the columns might vary , these would need to be chosen at the
start by the user via popup.

Grateful for any assistance.

Hi Colin,

Am Mon, 9 Jun 2014 23:06:17 +0100 schrieb Colin Hayes:

Quantity Ref

1 223
1 217
3 223
1 217
1 625

Would become :

Quantity Ref

4 223
2 217
1 625

in which columns are your values? Do you need a table with these both
columns without duplicates only? Then the easiest and fastest way is to
create a Pivot table from this two columns.


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

in which columns are your values? Do you need a table with these both
columns without duplicates only? Then the easiest and fastest way is to
create a Pivot table from this two columns.


Regards
Claus B.


Hi Claus

The two columns would vary from worksheet to worksheet , so 'Quantity'
and 'Duplicate' columns would need to be entered when the code is run.
Perhaps via message box for the user to enter the column letters.

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.

Grateful for your assistance as usual Claus.



Best Wishes

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

Hi Claus

OK I've put sample of what I mean here :

http://www.chayes.demon.co.uk/sample.xls

There are two tabs - before and after. The column queried for duplicates
in this example was O , with the result and update placed in M.

Thanks again Claus

Hi Colin,

Am Tue, 10 Jun 2014 17:22:00 +0100 schrieb Colin Hayes:

http://www.chayes.demon.co.uk/sample.xls

please have a look:
https://onedrive.live.com/?cid=9378A...121822A3%21326
for "sampleCH"


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