Removing Duplicates

blackgolf

Board Regular
Joined
Mar 1, 2012
Messages
65
Office Version
  1. 2021
Platform
  1. Windows
Hi Guys

I need to remove duplicates in Column B, but keeping the FIRST and LAST occurrence.
Example
All the duplicates in RED must be deleted, the complete row.

john
john
john
john
frank
frank
peter
peter
peter
peter

peter

If there are only 2 duplicates, like frank, then nothing must be deleted.
Many thanks for the help.
Chris
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
If your data is in A1:A11 you could use this formula in B1 then copy down. This will indicate which to delete. You would have to copy paste the formula as values if you were going to sort the data.

=IF(COUNTIF($A:$A,A1)>2,IF(OR(COUNTIF($A$1:A1,A1)=COUNTIF($A:$A,A1),COUNTIF($A$1:A1,A1)=1),"Keep","Delete"),"Keep")
 
Upvote 0
=IF(COUNTIF($A:$A,A1)>2,IF(OR(COUNTIF($A$1:A1,A1)=COUNTIF($A:$A,A1),COUNTIF($A$1:A1,A1)=1),"Keep","Delete"),"Keep")

Thanks Steve for formula. I am using Office 2013 and your formula gives me error (red text). I would have preferred VBA code, but a formula is also OK.
Chris
 
Upvote 0
Then im afraid I cant help as I cant replicate any problem with the formula if you use comma as a separator.
 
Upvote 0
Not sure what you try and tell me.
I have one column (A) to delete duplicates and your formulae is giving an error if I paste it in B1.
Where does the separator come in?
 
Upvote 0
Some countries use ; as a separator so the formula would be:

=IF(COUNTIF($A:$A;A1)>2;IF(OR(COUNTIF($A$1:A1;A1)=COUNTIF($A:$A;A1);COUNTIF($A$1:A1;A1)=1);"Keep";"Delete");"Keep")
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top