compare two columns in excel and remove duplicates

#Compare two columns in excel and remove duplicates how to

Demonstration of how repeated values are highlighted

I have manually highlighted the cells with identical values in Column B green for demonstrative purposes. Note how the values of the highlighted cells in Column A are repeated in Column B. Quickest Way to Find Duplicates in Two Columns in Excel. Preview of workbook after Conditional Formatting has been set The conditional formatting rule then will be applied to all values of the column and should look like this. If you are happy with the formatting you have specified click OK. You will be redirected back to the New Formatting Rule window and will be given a preview of the formatting you have selected. After having specified the format you want click OK. Change the cell color by clicking on the Fill tab and selecting a colorħ. It is possible to set a different cell color for duplicate values by clicking the Fill tab and selecting a background color. Click on the Format button and specify the format you want to set. Select either Duplicate or Unique from the left-hand side drop-down. =countif($B:$B, $A1) Enter the formula provided in the text boxĦ. On the Home tab, go to Conditional Formatting > Highlight Cells Rules > Duplicate Values. Proceed by entering the following formula in the ‘ Format values where this formula is true‘ box: Select the ‘Use a formula to determine which cells to format’ optionĥ. Select ‘ Use a formula to determine which cells to format‘ as a rule in the New Formatting Rule window.

#Compare two columns in excel and remove duplicates how to

I know how to remove duplicates within the same dataset, but have never compared between two. Once I have a 'modified' Master file, I then can merge the Master and Weekly files. If the same application id is in both datasets, then I would like remove them from the Master file. Select New Rule from the Conditional Formatting drop-down listĤ. I would like to compare the two files by a field name: appid. Select the New Rule option from the Conditional Formatting button drop-down list. Select the Conditional Formatting button under the Home tabģ. Select the Conditional Formatting button on the Home tab.

Highlight column A by clicking the column headerĢ. Click on the Column Header to highlight Column A. In this tutorial I will show you how to compare two columns in Excel so that the values that appear in both columns become highlighted and you can do something with those duplicate values.