Compare data from two columns or sheets

The Compare Columns or Sheets tool from the Remove Duplicates add-on for Google Sheets scans two 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.

Video: How to compare Google Sheets for duplicates

Before you start

  • 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.
  • 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.
Tip. This tool will help you compare two sheets or tables for duplicates or uniques.

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 within one sheet, 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.

How to use Compare Columns or Sheets

Start the tool

Find Remove Duplicates in the Extensions menu and click on the Compare columns or sheets tool to open it:
Run Compare sheets tool from the Google Sheets menu.

Tip. If you're a happy owner of the Power Tools add-on, you will find the utility in the Dedupe & Compare group:
Compare two sheets in Power Tools.

Step 1: Select your main table

Choose the sheet and the range with your main table:
Select the sheet with your main table.
You can choose any of the open sheets in the Select the main sheet drop-down list.

Note. The action you choose for the results will be applied to this sheet.

The add-on highlights the entire used range (cells with data before the first blank column and row) 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. In the latter case, you will see a corresponding dialog box that will let you pick the necessary cells:
Adjust the range.

Note. If you want to delete duplicates, not just color them, it's best to select the whole table so that the add-on removes entire rows avoiding data corruption.
Tip. Use the Auto select button to automatically pick the used range with all the data you have.

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.

Step 2: Choose the table for comparison

Choose the sheet with the table you want to compare to your main one.

Tip. This table will not be modified in any way.

Select the second table for comparison.

  1. The first drop-down lets you select the spreadsheet. It can be either your currently opened spreadsheet or any spreadsheet from Drive, even from the Shared one:
    Select a spreadsheet with the table to compare.

    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. Compare Columns or Sheets will scan your entire Drive 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:
    Import spreadsheets from Drive.

  2. The second drop-down invites you to select the second sheet from the list of all available sheets of this file.
  3. The Select your second column or table field will automatically pick up cells with data (till the first empty row and column) on the sheet you select:
    • If the second sheet is in another spreadsheet, either press Auto select to pick up the used range or click the Open link to open the file in a new browser tab, check the range, and then change it in the add-on manually:
      Change the range of the second sheet when it's in another file.
    • If both main and the second sheets are in the same file, you can change this range by entering another one from your keyboard, by highlighting a different range in the second table, or clicking the Select range icon and altering the range in the corresponding pop-up window.
      Tip. Use the Auto select button to identify the range with all your data automatically.

Choose Next to proceed to the next step, or Back to change the main table/column.

Step 3: Decide what to find

The add-on allows you to find unique or repeated values in both tables:
Choose Duplicate values to find all the repetitions.

  • Pick Duplicate values to look for the records you have in both tables/columns.
    Note. Only complete matches are treated as duplicates. Partial matches are not considered duplicates.

    Compare for duplicates.

    Note. The found records will be identified in the main sheet.
  • Choose Unique values to find those entries that are in your main table only:
    Compare for uniques.

Click Next to select the columns for comparison.

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 the key columns you want to compare in your sheets and pick the corresponding column in your second table.

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.

Choose columns you want to compare.

  1. If your tables have header rows, make sure to tick off the checkboxes Table 1 has headers and Table 2 has 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 both tables by using the Auto detect button.
  5. Check the Skip empty cells box to ignore duplicated 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 Table 2 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:
Fill found dupes with color.

Note. The action you select on this step will be applied to your main sheet.
  • 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 your main sheet:
    Add a status column.

    Tip. You'll be able to sort out duplicates 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.
    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.
    If you click the Select range icon, you'll be able to pick the top-left cell for the result directly from the sheet:
    Decide where to see 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 table with the last setting — Delete entire rows from the sheet.

Сlick Finish to find duplicate or unique values and mark them on the main sheet.

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 your main table:
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 often, most likely you will 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 for Compare Sheets.

  1. Name your scenario so you could understand what it does and quickly find it among other scenarios.
  2. Choose your first and second tables:
    • 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 Table 1 only.
    • Select specific sheets from the lists to compare particular tables.
  3. Identify ranges to be processed in both the first and the second sheets:
    • Select [All data] to automatically detect the entire used ranges for work.
    • For the tool to handle the exact cells, simply enter the desired ranges of cells.
  4. 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 Extensions > Remove Duplicates > Scenarios, select the required scenario and click Start.

Note. If you don't see Scenarios in the menu, just click Compare columns or sheets to open 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 Extensions > Remove Duplicates > 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.