Compare two columns in Google Sheets

Start the add-on

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

Run the owned collection from the Extensions menu:

• The fist two will feature Compare columns right in the menu:
  
• While Power Tools will have it located in the Dedupe & Compare group:
  

Fine-tune its settings

There's just one window with all the necessary comparison options:

1. Once you run the add-on, it automatically detects your whole table (based on the selected cell that is). You can enter another range right in this field.

   Or click the Select range icon on the right side of the field. The tool will shrink into a smaller pop up that will take the range you select with your mouse on the sheet:
   

   Note. You must enter the range containing at least 2 neigboring columns. If you pick the whole table, you'll be able to choose any 2 columns from it for comparison in Google Sheets, of course.
2. The Auto select button is the same on both this large main window and a smaller one above.

   It works as Ctrl+A shortcut: expands the range from the selected cell to the entire used range (till the first empty column and row on the way).

3. Use these 3 checkboxes to control the data that should be included to the column comparison:
   1. The 1st one is for when your selected range has headers. Use it to skip column names. Otherwise, the first row will be also checked against another column.
   2. Set up the same for any blanks in the columns. Check the box to skip empty cells or ignore the setting to match blanks as well.
   3. Match case will not just compare values from 2 columns in Google Sheets but also see if their text case matches. Identical records written in different text cases will be considered unique.
4. Select those two columns you'd like to compare. The add-on will show duplicate or unique values in the main column.
5. Pick the type of values you'd like to find:
   • Duplicate: those that appear in both columns.
     
   • Unique: those that occur in the main column BUT another one.
     
6. Choose what to do with the found values:
   
   • Color them with the selected hue.
   • Add a status column to the right of the main one:
     
   • Copy them to another location: new sheet, new spreadsheet, or a custom location (the cells of your choice within the current file).
   • Move them to a new sheet, or a new file, or specific cells in the same spreadsheet.
   • Delete entire rows from the sheet including cells outside your selected range.
   • Delete cells and shift data up only in the main column.
   • Clear values from cells in the main column without shifting or removing other data.

Hit Compare to start comparing two columns in Google Sheets.

See the result

When the add-on is done, you'll see a confirmation message saying how many duplicate or unique values have been colored and how they've been handled: