Delete rows that have duplicate data in certain columns

I have attached a sample of what I am talking about.

In this example, I have employee information. I want to group names together that meet criteria, which is, they all work at the same department, company, and state. I have highlighted in yellow the criteria.

When all three criteria are identical, i want the macro to group all names together and delete duplicates.

Please let me know if the attachment is not visible or if more info is needed.


Thanks!!

Please Login or Register  to view this content.

beautiful. thank you this is a huge help.

if i wanted to use this macro with additional criteria columns i would add them to the line

myKey = Array(2, 4, 5, 7...etc

is that correct? would i have to modify the code at all elsewhere?

That's it. No need to change any part of the code.

@ jindon,

Excellent !

Thanks....

jindon,

i am trying to adapt the code to my workbook, and i get a run-time error '9': subscript out of range. when i click 'debug', it highlights the second line of code that starts with a = sheets...


i have a few questions remaining:

1)could you explain the details of this line (a = sheets) so i could modify it correctly?
2)what part of code is responsible for pointing to the 'names' column? I need to modify what column has the names.
3)what is the purpose of Option Explicit?

Thank you

1) You need to replace "before" with actual sheet name.
It stores the data in variable "a" to speed up the process.

Please Login or Register  to view this content.

2) Name column should be included in an array "myKey" to make unique key.

3) It is always better to declare it. It forces to declare all the variable used in the module.

where in the code is the name column referenced?

Please Login or Register  to view this content.

First column in the data range.

okay last question, i promise!

instead of inserting the finished product into a new sheet, i want the macro to replace over the existing data, essentially shrinking down the range of data. I assume the last 'with' argument in the current code will be removed, but what must be added to ensure the consolidated version is pasted over the 'before' data??

Thank you very, very, very much!!!!

See the difference.

Please Login or Register  to view this content.

jindon,

i cannot make the code work in my real workbook. Please see the attachment, which contains only the headings of my real workbook. columns D,R,&S need to be compared and the names in col T need to be consolidated for duplicate rows.

aarodn

You should remember, one of the most important issue to write the code is to get the correct data range, otherwise the code will never work.

Your first sheet has Column A blank (I don't like it though), so I wrote

Please Login or Register  to view this content.

This gets the range of rectangle area includes "B1" until the boundary of blank row/column.
So the relative col reference within the data area will shift by 1 from the actual sheet col reference.

Now you have data from A1, which I prefer, so now the col reference is identical to the sheet.
Now Col.A = 1, Col.B = 2 etc. Was Col.B = 1, Col.C = 2 etc.