We help IT Professionals succeed at work.

VBA: manipulation duplicates in Excel

136 Views
Last Modified: 2018-09-10
Hello everybody.

I've a task as follows:

1) sheet 1: consider duplicates in column 2
2) duplicates in column 2: check if they have different values in column 1
3) if they have different values in column 1, copy entire row in sheet 2.

Look at the example in the attachment:

double.png
Beta, Gamma, Ics, Lambda and Omega have duplicates.
But only Gamma and Lambda have different values on column 1, so copy them on sheets 2.

Thank's for your help.
Comment
Watch Question

Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
Can you upload this file instead of me typing this thing again so that I can test it?
Paolo CrossiAdministrative employee

Author

Commented:
Here the file.
Exp-double.xlsx
Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Paolo CrossiAdministrative employee

Author

Commented:
Sorry I'm late for the answer, but I've needed time to study the code you've proposed.

Put in practical terms, it seems to work (I've tested it 3-4 times with huger amount of data).

Now, the theory:

dws.Range("A2").CurrentRegion.Offset(2).Clear

Open in new window

This is just to clear the sheet 2, I suppose.



dict.Item(x(i, 1)) = ""

Open in new window

Why = "" ?



n = Application.CountIf(rng, rng.Cells(1))

Open in new window

Why the second argument is rng.Cells(1)? Is (1) an offset?
Subodh Tiwari (Neeraj)Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015

Commented:
This is just to clear the sheet 2, I suppose.
dws.Range("A2").CurrentRegion.Offset(2).Clear
Correct.

dict.Item(x(i, 1)) = ""
Why = "" ?
Because we only need unique keys from Column B not their values.

n = Application.CountIf(rng, rng.Cells(1))
Why the second argument is rng.Cells(1)? Is (1) an offset?
No. The second argument in Countif is the value you are trying to count in the first argument rng. rng.Cells(1) would be the first cell in column A in the rows returned after applying the filter in column B.
Paolo CrossiAdministrative employee

Author

Commented:
Thanks for helping me.
Subodh Tiwari (Neeraj)Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome Paolo!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.