Compare data from two (or more) columns or sheets

Compare Sheets for Duplicates scans two or more columns or sheets of your choice for duplicated or unique data. The results can be colored, deleted, copied, or moved to another location.
Compare sheets or columns in Google Sheets.

Tip. You'll find this tool in all our collections: Compare Sheets, Remove Duplicates and Power Tools.

Before you start

Duplicates = complete matches

Only complete matches are treated as duplicates. Partial matches are not considered duplicates. And keep an eye out for excess spaces: they may interfere with results as well. Our Remove tool will help you get rid of them.

Google Sheets cell limit

Please bear in mind that Google Sheets limits the total number of possible cells in your file up to 10 million cells.

Explore other deduplication tools

How to use Compare Columns or Sheets

Start the tool

Find Compare Sheets in the Extensions menu and run Compare sheets for duplicates:
Run Compare sheets tool from the Google Sheets menu.

Step 1: Select sheets to compare

Choose all the sheets and ranges you'd like to compare:
Options of the first step.

  1. To select all sheets from the tree view for comparison at once, tick off the Sheets checkbox.
  2. To add more sheets to the tree view, click Add files from Drive.

    Tip. You can quickly locate the desired spreadsheet in Drive using the quick search field.

    Just enter the part of the file name into the Search field of the Import files from Drive window and hit Find. The tool will scan your entire Drive, shared files or Drives (if any), and even starred items and display the list of all files with partial or complete name matches.

    If you're not sure where these spreadsheets are located, simply hover your mouse cursor over the spreadsheet of interest and you'll see a tip with its full path:
    Quickly find the files by their names.

    Tip. To add several spreadsheets from your Drive to the add-on in one go, click on them by holding Ctrl (for non-adjacent) or Shift (for adjacent) pressed on your keyboard. The files will be highlighted in light blue. Then press Add to see them in the tree view.
  3. If you change your mind about any spreadsheet, select it and click Exclude to remove it from the comparison.
  4. By default, the add-on compares All data (till the first empty row and column) from each selected sheet. To alter it, click this field and do one of the following:
    • Enter the range address in this field using your keyboard.
    • Click the Select range icon and pick the necessary cells right on the sheet.
    • Click the Select range icon and hit Auto select. The add-on will automatically pick the used range with all the data you have:
      The Select range window.

      Note. If you want to delete duplicates rather than just color them, it's best to select the whole table so that the add-on removes entire rows avoiding data corruption.

Click Next to proceed to the second step..

Step 2: Select the main sheet

Choose the sheet that will be treated as your main one:
Step 2 for choosing the main sheet.
This sheet will serve as a reference for comparison with all other sheets. The results will show the relation between the main and other compared sheets.

Tip. The sheets are organized by the spreadsheets they belong to, which can be particularly beneficial if there are multiple sheets with identical names.

Choose Next to proceed to the next step, or Back to change the comparison sheets.

Step 3: Decide what to find

The add-on allows you to find unique or repeated values in all tables:
Find duplicate or unique values.

  • Pick Duplicate values to look for the records that exist in the main and every other compared sheet.
    Note. Only complete matches are treated as duplicates. Partial matches are not considered duplicates.

    Compare all sheets for duplicate values.

    Note. The found records will be identified on the sheets selected on the last step.
  • Choose Unique values to find those entries that appear in every other sheet but the main one:
    Compare all sheets for unique values.

Click Next to select the columns to compare.

Step 4: Pick the columns to compare

On this step, you will see a list of columns in your main table. Select the checkboxes next to those key columns that you want to compare in your sheets and pick the related columns from other compared sheets.

Tip. If you select more than one column, a row will be considered duplicate if the values in all these columns are the same:
Key columns to compare.

Choose columns you want to compare.

  1. If your tables have header rows, make sure to tick off the checkboxes Main table has headers and Other tables have headers respectively to see the column titles. If you don't have headers, unselect these boxes to display column names.
  2. You can use the 1st row content fields to see what kind of data you have in your main table columns.
  3. If you want to look for complete row duplicates, select all columns by using this checkbox in the header row.
  4. Automatically select all columns that have the same headers in all tables by using the Auto detect button.
  5. Check the Skip empty cells box to ignore duplicate blanks in the selected columns. Otherwise, blank matches will be considered duplicates.
  6. Use the Match case option to consider those records that differ only in the text case as uniques.
  7. Make sure to indicate the corresponding Other tables columns by clicking the down arrow for each of them and picking the necessary column from the drop-down list.

Once everything is set, press Next to proceed to the last step.

Step 5: What to do with the results

There are 7 (seven) ways to deal with the found values:
The last step with all the actions.

Choose the action

  • Color the rows with the found values by picking the Fill with color option. Click on the down arrow next to this option to choose a hue you'd like to use.
  • Add a status column to the found records:
    Add a status column right after your table.

    Tip. You'll be able to sort out duplicates/uniques easily after that.
  • Decide to Copy to another location and have the results in a new sheet, new spreadsheet, or any custom location (existing sheet in the current file).

    When copying from multiple sheets to a new spreadsheet, there's an extra checkbox: Values from each table to separate sheets. Use it to put the dupes/uniques from each compared table into a separate file.

    Tip. The Custom location option lets you pick a destination cell in one of the existing sheets:
    Choose one of the existing sheets and its cell or range.
    Just click the Select range icon and pick the top-left cell for the result directly from the sheet:
    Select the range for the result.
  • The same goes for the Move to another location option. The values will be cut and pasted to a place of your choice.
  • Pick Clear values to remove the found records in the selected columns and leave all other data intact.
  • You can also remove all rows with the found dupes using the Delete rows within selection option.
  • Or have the entire rows removed from the sheet even outside your selected tables with the last setting — Delete entire rows from the sheet.

Add cross-sheet links is available for each action except deleting. It will insert hyperlink references next to the found dupes so you could quickly navigate across all found instances in the compared sheets and see the source sheets of each record.

Apply the action to

Since the add-on compares multiple sheets now and looks for the values across them all, you can choose where those dupes or uniques will be processed once they're found:

  • Main sheet. To color, remove, etc. found values only in the main sheet:
    Color dupes on the main sheet only.
  • Other compared sheets. To process found dupes or uniques on all sheets but the main one:
    Color dupes on all sheets except the main one.
  • All sheets. To apply the action to all duplicate or unique values across all sheets: main and other compared sheets:
    Color dupes across all compared sheets.

Click Compare to find duplicate or unique values and process them with the chosen action on the chosen sheets.

See the result

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 them:
See the number of found dupes in the result message.
To save these settings and use them in the future, click Save scenario.

Tip. If you'd like to run the add-on again, click on the Start over button.

How to work with scenarios

If you compare columns & sheets often, most likely you'll want to run the add-on without going over the same steps and choosing the same settings over and over again.

Scenarios will help you with that.

What is a scenario

A scenario is a saved set of those options that you select in the add-on on each step. You will have a chance to save the settings right after the add-on processes your data. Then, you just run this scenario and the tool compares your data. Pretty cool, huh? :)

Save the scenario

When the add-on finishes combining duplicate rows and shows you the result message, click Save scenario:
Save the settings into your scenario.
You will see a short summary of all options you have used just now — this is your scenario preview:
Save the setting into the scenario.

  1. Name your scenario so you could understand what it does and quickly find it among other scenarios.
  2. Choose your tables to compare:
    • Pick [Selected sheet] and the add-on will treat your currently active sheet when running the scenario as a main one.
      Note. This option is available for Main sheet only.
    • Select specific sheets from the lists to compare particular tables.
  3. Identify ranges to process in the main and other compared sheets:
    • Select [All data] to automatically detect the entire used ranges for work.
    • To handle the exact cells, simply enter the desired ranges of cells.
  4. Scroll down to look through the options you're about to save to make sure everything is correct:
    Review all settings of the future scenario.

    Note. These options cannot be changed. To modify them, you will need to restart the add-on and pick the correct settings on all steps over again.

Once you're all set, click Save and the scenario will be created.

Run your scenario

To start the scenario, go to your add-on (Compare Sheets, Remove Duplicates or Power Tools) in the Extensions menu, find Scenarios, select the required scenario and click Start.

Note. If you don't see Scenarios in the menu, just run the add-on itself once. This is necessary for new spreadsheets and those where the add-on hasn't been run yet. Your scenario (and all future ones) will then appear in the menu automatically.

Start the scenarios for Compare Sheets.
This will run Compare Sheets with all the necessary settings right away.

Once it's done, you will get the result message saying what scenario has just worked and what it's processed:
Result for the Compare Sheets scenario.

Manage scenarios

To view the scenario or to change the sheets and the ranges for comparison, go to the same Scenarios menu, pick the scenario and select Edit this time:
Edit the Compare Sheets scenario.
You will see the same scenario outline:
Rename the scenario or change tables and ranges.
You can give it a new name and select other sheets & ranges.

If you make any changes, press Save to keep them. Click Run to start the scenario right away, or hit Delete to remove it completely.

Responses

Hi! Is there a way to set up trigger so that the scenario would run automatically every time that the data has been altered? Thank you in advance.

Hi Soimesa,

Thank you for your question. I'm afraid it's currently impossible to process data with our tools or scenarios automatically. You need to run it each time you want to do that.
However, I have forwarded your idea to our developers to see if they could implement it. Thank you.

Need to find duplicates in multiple (six) sheets within a spreadsheet but I can only choose between two.
Plus, would be great if could make the duplicate conditional. i.e.: color duplicate in sheet 1 if appears in (at least) 3 of the 6 sheets.

Realise it's a bit complex, but would go a long way.

Any way to do that?
Thanks

Hello Pedro,

Thank you for your comment.

Unfortunately, the current version of Compare Columns or Sheets can work only with two sheets at a time. You need to run the add-on several times to process more sheets.

However, there are scenarios available in the add-on. You can 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. Please see how to work with scenarios here.

I will also forward your request to our developers and they will look into the possibility to improve the add-on functionality. Thank you.

Hello Pedro,

I'm happy to let you know that the feature has been implemented. The updated Compare Sheets for Duplicates tool from the collection will do that. Please review the help page above once again to see what's new in the tool.
Let me know if you have any questions or need any assistance.

Edwin Ramaker says:
November 17, 2020 at 8:55 am

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.

how can I use your addon to compare sheets within 2 different google sheet files?

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?

Katerina Bespalaya (Ablebits Team) says:
December 12, 2019 at 1:08 pm

Hello Jake,

I've just replied to you by email. Please provide us with the information requested in the email message. Thank you.

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.

Post a comment

Seen by everyone, do not publish license keys and sensitive personal info!

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.