CODEWITHSUNDEEP
excelvbaduplicates
wra
wra

Reputation: 257

How to remove duplicates depending on values in another column(s)?

[Week]           [User]

Week 1           Bryan
Week 1           Sam
Week 1           Bryan
Week 1           Will
Week 2           Bryan
Week 2           Will
Week 2           Will

I'm looking to be able to count unique users per week. Therefore, I'd like to remove duplicates, but per week. So in the example above, Week 1 should only have Bryan, Sam, and Will, and Week 2 will have Bryan, and Will. Regular "Remove Duplicates" will remove names across weeks as well which is what I don't want. So is there someway to count and/or remove duplicates in a column, based on value(s) in another column? VBA code is also very much welcome.

Thanks in advance!

Upvotes: 0

Views: 1349

Answers (3)

user8463770
user8463770

Reputation: 11

If you have a large data set and you want to accomplish this in VBA, you can achieve this by using the RemoveDuplicates method.

Try this:

Dim LR as Long 

LR = activesheet.usedrange.rows.count

Range(“A1:B” & LR).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33692

You can use Remove Duplicates to achieve what you described in your post.

Select your range, and then select both columns, click the check mark V next to "My data has headers":

enter image description here

Result after running:

enter image description here

Upvotes: 0

OES
OES

Reputation: 321

Insert Pivot Table. In a Rows put [Week] and below that put [User]

It would automatically give you list of weeks and users (only once).

Upvotes: 1

Related Questions