IS there are a way to delete duplicated values, not only one of the duplicated values but all of them? For example if there are 3 "apples", I want to delete all "Apples" and leave only unique values.
The easiest approach, without VBA code, is to have an extra column. There, you use a COUNTIF function to count the number of occurrences of each value. An autofilter could then be used to filter on those results >1, and delete the filtered rows. (Or convert the IF formulas to values, sort the whole thing and delete cells with the IF strictly bigger than 1).
Thanks it works but when I copy and paste the formula for the rest of the rows, formula automatically change the range too. Do you know how to paste quickly but not messing the formula?
For example: the first cell's formula is
=COUNTIF(A2:Z41,AA1)
When I drag it down the second cell is
=COUNTIF(A3:Z42,AA2)
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.
