Augmenting an integer in a column to reflect duplicates in another

  • Thread starter Thread starter Colin Hayes
  • Start date Start date
C

Colin Hayes

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
Click to expand...

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.
 
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.
Click to expand...


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.
Click to expand...

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.
 
Claus Busch said:
Hi Colin,

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


please have a look:
https://onedrive.live.com/?cid=9378AAB6121822A3&id=9378AAB6121822A3!3
26#cid=9378AAB6121822A3&id=9378AAB6121822A3%21326
for "sampleCH"


Regards
Claus B.
Click to expand...

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.
Click to expand...

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


Regards
Claus B.
 
Claus Busch said:
Hi Colin,

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


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


Regards
Claus B.
Click to expand...

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.
Click to expand...

have another look in OneDrive


Regards
Claus B.
 
Claus Busch said:
Hi Colin,

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


have another look in OneDrive


Regards
Claus B.
Click to expand...

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:
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.
Click to expand...

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
Click to expand...
 
Claus Busch said:
Hi Colin,

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


please have a look:
https://onedrive.live.com/?cid=9378AAB6121822A3&id=9378AAB6121822A3!3
26#cid=9378AAB6121822A3&id=9378AAB6121822A3%21326
for "sampleCH"


Regards
Claus B.
Click to expand...

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
 
Claus Busch said:
Hi Colin,

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


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


Regards
Claus B.
Click to expand...

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.
Click to expand...

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.
 
Claus Busch said:
Hi Colin,

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


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.
Click to expand...

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?
Click to expand...

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


Regards
Claus B.
 
Back
Top