How to delete duplicate records using criteria for multiple rows

Ardiko,
I may be able to help with problem 1, but not the other two problems. Do Range (A-L) of two lines need to be equal so as to be duplciates, or just the years(Column G)? In other words, how do you identifay a cell, or a row is a duplicate?

Thanks AB33 for the reply. If the tree has the same Expt (a), Plot (b), Species (c), Treat (j), Year(g) and Code (k) it is a duplicate. I think I could make a IF statement myself to solve it but my main concern is that the data will be all over the place and I need to find the duplicates without having to sort and to automate the removal of them. If you can help with the initial problem of outlining them that would be great.

This will do and will look from Ato L, but can be amended if you just want to look at a,b,c,j. and g. Try it

Please Login or Register  to view this content.

Cheers AB33! It works perfectly! I couldnt ask for one last final request if it is not time consuming? I would like to have the option of highlighting all duplicate cells before deletion with a "X" in the M column so I can filter for it. This would be an additional measure for myself so I know what I'm deleting just in case.

Anyway no worries if you don't have the time to look at that.

Once again thanks for your help and I'll re-edit this post so it only has the one problem and post my other ones in a separate thread.

+rep

Ardiko,
The issue I have is when you indicate a row is a duplicate by colouring it, or indicating using "X", the next line of the code will delete it, unless ofcourse, you want to copy it to a separate sheet. I suggest you do the following: You have two separate codes, One is just to show you which rows have duplicates and the other code is to delete these rows.
Change this line of code and try it and see what you think.

Change this line of the code

Please Login or Register  to view this content.

INTO

Please Login or Register  to view this content.

AB33 once again many thanks! This is very helpful!

Would you be able to show me how to paste the discovered duplicates on to a tab beside it?
I would also like to be able to call this sheet 'Duplicates'

Thanks again!

Also AB33 I have a problem with the current code.

In column L "Meas" for measurement a duplicate tree can have a different measurement if it has the same tree number with the same values on the same row. I might be bringing in two datasets for the same year but one could be updated therefore some of the plots will have very similar data but never will have more than one plot.

Please see my updated spreadsheet.
Validation Program v2.xlsm

Cheers

Ardiko,
I am not sure what the issue you have. I have re-run the code on both sheets and could not find any duplicates. I have also added a duplcate row to test it and not only works, but also managed to copy the duplicate row in to duplciate sheet.If you do not want column L to be part of the test for the duplcate I can remove it from the code.

Please see the attached.

Hi AB33,
I suppose I don't want it to include column L then as a duplicate should show up with the other columns matching. If you can make that change it'd be great.

Once again thanks for all your help.

Okay, I have now removed column L from the criteria and found 3 duplicates and are copied to the duplciates sheet. Please find attached.