The Compare Columns or Sheets tool from the Remove Duplicates add-on for Google Sheets scans two columns or sheets of your choice (within one spreadsheet only) for duplicated or unique data. The results can be colored, deleted, copied, or moved to another location.
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.
If your task is to find identical or unique rows in a table, take advantage of the Find duplicate or unique rows add-on or its one-step version Quick Dedupe instead.
To look for dupes or uniques in all single cells all over an entire spreadsheet, Find duplicate or unique cells is what you should use.
To merge or consolidate unique records that refer to multiple duplicate values on different rows, turn to Combine Duplicate Rows.
Find Remove Duplicates in the Add-ons menu and click on the Compare columns or sheets tool to open it:
Choose the sheet and the range with your main table:
You can choose any of the open sheets in the Select the main sheet drop-down list.
The add-on highlights the entire used range in the sheet of your choice automatically and shows it in the Select your column or table field. You can alter it by changing the selection manually, entering the range address in this field, or by clicking the Select range icon. You will then see a corresponding dialog box that will let you pick the necessary cells:
You can choose to back up the original table by ticking off the Create a backup copy of the sheet checkbox.
Click Next to proceed to the second step.
Choose the sheet with the table you want to compare to your main one:
You can choose any of the open sheets in the Select the second sheet drop-down list.
The Select your second column or table field shows the currently highlighted range. Define another one in this field by entering it on your keyboard, picking it directly in the sheet, or by using the Select range icon. The latter will open a dialog box so you could pick the necessary cells:
In case you want to change the main table/column, click the Back button.
Otherwise, choose Next to proceed to the next step.
The add-on allows you to find singular or repeating values in the tables:
Click Next to go on to the next step.
On this step, you will see a list of columns in your main table. Select the checkboxes next to the key columns you want to compare in your sheets and pick the corresponding column in your second table.
Once everything is set, press Next to proceed to the last step.
There are six ways to deal with the found values:
Сlick Finish to find duplicate or unique values and mark them on the main sheet.
Once the add-on completes the search, you will see the summary of the results with the number of found values and the action that was applied to your main table:
Responses
Buenos días, quisiera que el proceso de ver los datos únicos, se automatizará, en vez de realizarlo de forma manual, como puedo programarlo????
Hello Guatavo,
I'm afraid that it's impossible to run our add-on automatically, but recently we've introduced scenarios for our Remove Duplicates that can help you automate repetitive tasks and check for duplicates in a click. Just save the settings you use most often and give this set a name – this is your scenario. Once you run it, your sheet will be processed right away, and you won't need to go over the same steps again. You can view, edit, and delete scenarios quickly anytime.
Feel free to check the detailed instructions on how to work with scenarios on this help page.
If you encounter any difficulties or need our assistance, please let us know. We'll do our best to help.
Hi,
I just purchased your product but it doesn't reflect on my google sheets.
Kindly help asap.
Best,
Julia
Hello Julia,
Thank you for contacting us.
For the add-on to appear in your Google Sheets, you need to install it first. Since you have a key, please also activate the tools.
Hello,
This add-on doesn't seem to work consistently.
Example: I'm trying to compare two worksheets. When comparing by 'entire worksheets' it reports no duplicates found (I've ensured the correct columns are matching). However, when I choose one column at a time and compare the worksheets it will find duplicates...
This is frustrating as there are numerous columns I'm trying to compare. The only workaround I have is querying one column at a time, which defeats the purpose of this tool.
Is there a solution for this?
Hello Jake,
I've just replied to you by email. Please provide us with the information requested in the email message. Thank you.
how can I use your addon to compare sheets within 2 different google sheet files?
Hello George,
Thank you for your question. Sorry, the add-on works within one spreadsheet only.
Hello George,
Since the tool lets you compare data within one file, you may consider bringing everything together before comparing. We have a special add-on for that as well – Combine Sheets. Here's a tutorial page in case you're interested.
i have 1 sheet employee with foto from google drive
i have 1 sheet up to date called employee new.
how can i sync the employee with the employee new ?
all data is in rows:
a1 name a2 number a3 date of birth etc (both sheets same)
so i want the not active personel out and new added below
can this be done?
greetz from Holland
Hello Edwin,
Thank you for contacting us. If I understand your task correctly, our Merge Sheets may help you solve it. It allows you to update the data in the main table with the data from the lookup table based on the key column(s). You can install it from Google Workspace Marketplace and test it out for 30 days for free to see if it suits your needs.
However, it'll replace the non-active users with the new ones only if they have some matching criteria (for example, if their IDs are identical). Otherwise, you'll need to manually remove the old employees. Please also check the detailed instructions on how to use Merge Sheets on this help page: https://www.ablebits.com/docs/google-sheets-merge-two-sheets/
Hope you'll find our add-on helpful. Let us know if you have any further questions or need some assistance.