This additional tool to the Remove Duplicates will help you track down all duplicate or unique cells in your Google Sheets.
We care about your data and suggest you always create backup copies of your spreadsheets. A special option of the add-on will do that for you if you select it.
Please bear in mind that Google Sheets limits the total number of possible cells in your file up to 5 million cells.
In case you need to look for identical rows rather than single cells, we have another tool for that: Find duplicate or unique rows.
To compare two sheets or tables for duplicates or uniques, take advantage of the Compare columns or sheets tool instead.
To merge or consolidate unique records that refer to multiple duplicate values on different rows, turn to Combine Duplicate Rows.
Use the Google Sheets menu and go to Add-ons > Remove Duplicates > Find duplicate or unique cells:
Choose the range of cells you want to check for duplicates or uniques:
You'll see the entire used range highlighted by default.
Adjust it to your needs by clicking the Select range icon and entering other range manually or selecting it the spreadsheet of interest:
To preserve a copy of your spreadsheet intact just in case, decide in favor of the Create a backup copy of the sheet option.
Click Next to go on to the second step.
Choose the type of data you want to find in your spreadsheet:
Tick off the option to Skip blanks and the add-on will exclude all empty cells from the search. Otherwise, they will be treated as uniques or duplicates, depending on their frequency within your data.
If you have the same records written in different cases, and you want to have them as different values, check the option to Match case as well.
You can return to step 1 by clicking Back.
Press Next to proceed to the final step.
The add-on offers 4 ways to process the found duplicate or unique cells:
Pick the radio button next to the action you'd like to apply:
Click Finish to find duplicate or unique values.
Once the add-on completes the search, you will see the summary of the result with the number of found values and the action that was applied:
How can I remove duplicates but still keep the row blank? I have an email list with a lot of duplicate emails, but only some of the entries have a name associated in the First Name column. When I try to remove duplicates from the Email column, the Email column ends up a lot shorter than the First Name column, and the correct names are no longer associated with the corresponding emails.
Thank you for your help.
Thank you for your question, Kirsten.
If I understand your task correctly, you need to use Remove Duplicate Rows, not the tool described above. However, neither of add-ons shifts cells of one column when clearing the values.
For us to be able to assist you better, please share your spreadsheet with us (gapps.ablebits@gmail.com) for testing. Also, please send us the screenshots of the options you choose on each step of the add-on.
We'll look into the problem and do our best to make the tool work for you.
Thank you.
Okay... I get as far as select a range, and then can't move on. What gives?
Thank you for reporting the problem to us, Steve.
Please email us to support@ablebits.com with the screenshot of what you see when you select the range. The screenshot of the data you have in the range would also be useful.
We'll do our best to help.
Rather than seeing results in a single operation.
I want it to be applied in real time when I am writing on the sheet.
Is there any way I do not know?
Thank you for your interest in our product, Kwon.
Currently, it's impossible to run the add-on automatically. You need to run it each time you want to remove duplicate cells.
I should mention that our Remove Duplicate Rows contains scenarios that let you save the options you select most often and run them in a click. Depending on your task, you may find this feature helpful.
