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

In article , Claus Busch
writes
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...B6121822A3%213
26#cid=9378AAB6121822A3&id=9378AAB6121822A3%213 26
for "sampleCH"


Regards
Claus B.

Hi Claus

OK thanks for this.

I'm getting an error of ' Object does not support this property or
method'.

It's in this line , coloured yellow in the debugger:

.UsedRange.RemoveDuplicates Columns:=ColBCode.Column, Header:=xlYes


I'm using Excel 2003. Maybe that's the issue.



Best Wishes


Colin

Hi Colin,

Am Tue, 10 Jun 2014 19:51:08 +0100 schrieb Colin Hayes:

I'm using Excel 2003. Maybe that's the issue.

yes, that is the issue. Sorry, that I didn't recognized it.
Please look again in OneDrive. I corrected the issue


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

In article , Claus Busch
writes
Hi Colin,

Am Tue, 10 Jun 2014 19:51:08 +0100 schrieb Colin Hayes:

I'm using Excel 2003. Maybe that's the issue.

yes, that is the issue. Sorry, that I didn't recognized it.
Please look again in OneDrive. I corrected the issue


Regards
Claus B.

Hi Claus

OK fantastic. Works brilliantly! You are a genius...

BTW I wanted also to colour the rows where changes have been made to
red. So where the quantity has been updated , that row would be coloured
red.


I'm trying to add

..Font.ColorIndex = 3

but I'm not sure where.

Clearly , unchanged rows can stay as they are,


Thanks Claus.


^_^

Hi Colin,

Am Tue, 10 Jun 2014 21:05:12 +0100 schrieb Colin Hayes:

BTW I wanted also to colour the rows where changes have been made to
red. So where the quantity has been updated , that row would be coloured
red.

have another look in OneDrive


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

In article , Claus Busch
writes
Hi Colin,

Am Tue, 10 Jun 2014 21:05:12 +0100 schrieb Colin Hayes:

BTW I wanted also to colour the rows where changes have been made to
red. So where the quantity has been updated , that row would be coloured
red.

have another look in OneDrive


Regards
Claus B.

Claus

You are going to hit me!

The routine needs to *ignore* blanks in the Bcode column.

Sometimes some cells will be empty in this column , but it mustn't count
them as the same and then adjust quantities. This is really important.

Can it be made to disregard blanks in the Bcode column?

Sorry for being a nuisance....

Hi Colin,

Am Dienstag, 10. Juni 2014 22:20:50 UTC+2 schrieb Colin Hayes:
In article , Claus Busch

Sometimes some cells will be empty in this column , but it mustn't count

them as the same and then adjust quantities. This is really important.

sorry for the delay, my news server is off.
I modified the code and the rows with blank cells will remain and will not be count.
Have another look in OneDrive

Kind Regards
Claus

In article , Claus Busch
writes
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...B6121822A3%213
26#cid=9378AAB6121822A3&id=9378AAB6121822A3%213 26
for "sampleCH"


Regards
Claus B.

Hi Claus

OK that's excellent - thanks.

There remains one small issue , though.

It seems that while the routine successfully ignores blanks in the Bcode
column , it is also making quantity 0 for these items.

These items do exist , it's just that they don't have a Bcode. For this
reason the Quantity column needs to left untouched for these items. I'm
not sure why the routine would make them 0.

Would you be able to look into this?

Many thanks.



Best Wishes

HI Claus

I put another sample to show the ideal outcome of the macro.

It's here :

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



Best Wishes


Colin

Hi Colin,

Am Wed, 11 Jun 2014 14:08:27 +0100 schrieb Colin Hayes:

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

sorry that I misunderstood the problem. I thought of empty rows and not
empty cells.
Have another look for "sample2CH"


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

In article , Claus Busch
writes
Hi Colin,

Am Wed, 11 Jun 2014 14:08:27 +0100 schrieb Colin Hayes:

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

sorry that I misunderstood the problem. I thought of empty rows and not
empty cells.
Have another look for "sample2CH"


Regards
Claus B.

Hi Claus

OK that's it - thank you.

I see you included a reference to the Title column. This content can
vary even for the same item , and different items can have the same
title. For this reason , it's not a reliable reference and will skew the
results.

Really the only reference should be to the Bcode column , and this would
decide the quantity. I don't want to trouble you more on this , so I'll
have try to remove the reference to the Title column.

Thanks Claus.

Hi Colin,

Am Wed, 11 Jun 2014 19:03:02 +0100 schrieb Colin Hayes:

I see you included a reference to the Title column. This content can
vary even for the same item , and different items can have the same
title. For this reason , it's not a reliable reference and will skew the
results.

ok, I see it.
Have another look. Now only the rows with values in BCode will have
formula. The others remain only copied.


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

In article , Claus Busch
writes
Hi Colin,

Am Wed, 11 Jun 2014 19:03:02 +0100 schrieb Colin Hayes:

I see you included a reference to the Title column. This content can
vary even for the same item , and different items can have the same
title. For this reason , it's not a reliable reference and will skew the
results.

ok, I see it.
Have another look. Now only the rows with values in BCode will have
formula. The others remain only copied.


Regards
Claus B.

Hi Claus

OK it's perfect. Thank you for your time and expertise.

I'm very grateful.



Best Wishes


Colin

BTW - is there a way to make the code act the first or second sheet
without the code referencing specific names for the sheets a specific
name?

We called the first sheet 'Before' and the second sheet "After".

This is fine of course until the wb we run it on has sheets with
different names. If the code could act on the first and second sheets
whatever their name is , this would make it more universal and avoid
errors when the sheet names don't match the ones given in the code

Hi Colin,

Am Wed, 11 Jun 2014 23:50:29 +0100 schrieb Colin Hayes:

BTW - is there a way to make the code act the first or second sheet
without the code referencing specific names for the sheets a specific
name?

change Sheets("Before") to Sheets(1) and Sheets("After") To Sheets(2)
or look again in OneDrive (It is changed there)


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

In article , Claus Busch
writes
Hi Colin,

Am Wed, 11 Jun 2014 23:50:29 +0100 schrieb Colin Hayes:

BTW - is there a way to make the code act the first or second sheet
without the code referencing specific names for the sheets a specific
name?

change Sheets("Before") to Sheets(1) and Sheets("After") To Sheets(2)
or look again in OneDrive (It is changed there)


Regards
Claus B.


Hi Claus

OK I did this , but the code is giving Value! Errors in the formula in
the quantity column.

I think this is because Before! Is still mentioned twice in the last
part of the code. What would this need to be changed to?



Best Wishes


Colin

Hi Colin,

Am Thu, 12 Jun 2014 14:22:52 +0100 schrieb Colin Hayes:

OK I did this , but the code is giving Value! Errors in the formula in
the quantity column.

sorry, that is my bad. I forgot to change the formula.
Please have another look in OneDrive


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

In article , Claus Busch
writes
Hi Colin,

Am Thu, 12 Jun 2014 14:22:52 +0100 schrieb Colin Hayes:

OK I did this , but the code is giving Value! Errors in the formula in
the quantity column.

sorry, that is my bad. I forgot to change the formula.
Please have another look in OneDrive


Regards
Claus B.


Hi Claus

OK all fine now. Many thanks.



Best Wishes


Colin

In article , Claus Busch
writes
Hi Colin,

Am Thu, 12 Jun 2014 14:22:52 +0100 schrieb Colin Hayes:

OK I did this , but the code is giving Value! Errors in the formula in
the quantity column.

sorry, that is my bad. I forgot to change the formula.
Please have another look in OneDrive


Regards
Claus B.

Hi Claus

OK all fine now.

Many thanks!



Best Wishes


Colin