Deleting duplicate references with certain criteria

Hi

Please can someone advise. I have a spreadsheet with consecutive references. Some of those references are duplicates. I have already got a formula to identify those duplicates. However, in a separate column named 'CERTIFICATE', there is an entry on some of the duplicate records. I would like to remove the duplicate record without the entry in the 'CERTIFICATE' column if that makes sense, and leave the record with the entry in intact. Can someone provide me with a formula which will identify the duplicate column without an entry in the 'CERTIFICATE' column so I can remove it please.

I have attached a copy of the spreadsheet.

Thank you.

Put this formula in B2:

=IF(AND(COUNTIF(A:A,A2)>1,D2=""),"remove","")

and copy down.

Hope this helps.

Pete

Hi Pete UK.Thank you. This is exactly what I need. Much appreciated. Can you also explain how your formula works so I can understand it better. Cheers.

Hello

Please I am new to excel programming and would like to get started.

Can anyone help me with basic study materials for a beginner.

Originally Posted by AlexnL12

Hi Pete UK.Thank you. This is exactly what I need. Much appreciated. Can you also explain how your formula works so I can understand it better. Cheers.

The COUNTIF bit checks for duplicates, but the entry in D2 should also be empty for the message to be displayed.

Hope this helps.

Pete

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you may like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete