How To Compare Columns

In this tutorial, I will be showing you how to compare columns using different methods and how to remove duplicates from columns.

• COMPARING COLUMNS (SIMPLE COMPARISON)
• Step 1 - In the excel sheet below, we have three columns Column 1, Column 2 and the Comparison column which will display the result of the comparison. You can select create simple data in the excel worksheet below to follow up with this tutorial.

• Step 2 - In the comparison column, type in the formula equals to, the first cell address, equals to and the second cell address (that is =A2=B2) and press Enter on your keyboard to display the result. This formula output displays either TRUE or FALSE, that is if both cells are the same the result will be TRUE, but if they are different the result will be FALSE

• Step 3 - After pressing Enter on your keyboard the result will be displayed. In our case here it is FALSE because Cell A2 Book is not the same with Cell B2 Bed.

• Step 4 - Now instead of type the same formula again for the rest of the comparison, let flash fill it. Place you mouse point at the bottom right edge fo the cell and the mouse pointer will changed to a plus sign.

• Step 5 - Immediately the mouse pointer changes to a plus sign, click and drag down as shown in the image below.

• Step 6 - Once you get to the end of the column data, remove your finger from (or release) the click button and the comparison column will be auto filled with the appropriate results as shown in the image below.

• Step 7 - ALL DONE!

What if we want to be able to specify the text that will display in the after comparing. Using the method gives us the limited option as our comparison result can only be TRUE or FALSE. So, if for instance we want the result to display SAME/NOT SAME, or MATCH/MISMATCH, RIGHT/WRONG etc then we have to use a different method.

• COMPARING COLUMNS (USING IF FUNCTION)
• Step 1 - In the excel sheet below, we have three columns Column 1, Column 2 and the Comparison column which will display the result of the comparison. You can select create simple data in the excel worksheet below to follow up with this tutorial.

• Step 2 - Type in =IF in the comparison column. The IF function takes in three parameters. First is the logical test, second is the value to display if the logical test returns true and the third is the value to display if the logical test returns false.

• Step 3 - Type in the cell address of the first column you want to compare, or simply click on the cell.

• Step 4 - Next add/type the equals to sign

• Step 5 - Type in the cell address of the second column you want to compare, or simply click on the cell.

• Step 6 - Next add/type the comma sign, and proceed to Step 7 which is the second parameter to be entered if the logical test returns TRUE.

• Step 7 - Inside or in between double quotation marks, type in the value to be displayed if both cells are the same. In this case I will be using MATCH if the cells are the same.

• Step 8 - Nex add/type another comma sign as shown in the image below.

• Step 9 - Inside or in between double quotation marks, type in the value to be displayed if both cells are not the same. In this case I will be using MISMATCH if the cells are not the same.

• Step 10 - Enter the closing IF parenthesis and press the Enter on your keyboard.

• Step 11 - The result will be displayed immediately. In our case here it is FALSE because Cell A2 Book is not the same with Cell B2 Bed.



NOTE:

In case yours didn't display or an error occurred, this might be due to the fact that you missed one or two of the punctuation marks, either a comma, parenthesis of double quotation mark.

• Step 12 - Now instead of type the same formula again for the rest of the comparison, let flash fill it. Place you mouse point at the bottom right edge of the cell and the mouse pointer will changed to a plus sign.

• Step 13 - Immediately the mouse pointer changes to a plus sign, click and drag down as shown in the image below.

• Step 14 - Once you get to the end of the column data, remove your finger from (or release) the click button and the comparison column will be auto filled with the appropriate results as shown in the image below.

• Step 15 - ALL DONE!

What if there is a case where we want our comparison to be exactly the same checking for both uppercase and lower case and data type. What do we do?

• COMPARING COLUMNS (USING IF FUNCTION WITH EXACT)
• To make sure that our comparison is exactly the same we have to add the EXACT while using the IF function in the above method below.
• Step 1 - In the excel sheet below, let compare the data using IF function with EXACT function. Now type in the pre-defined formulas (combination of the functions) with its appropriate data as shown in the image below.
  That is: IF(EXACT(A1,B1),"MATCH","MISMATCH")

• Step 2 - The result will be displayed.
  Now using the flash fill method, place your mouse at the bottom right edge of the cell, click then drag and release the click button for the result to be displayed.
  Cell A1 and B1 are the exactly the same (MATCH). Cell A2 and B2 have the same spelling but Cell A1 is in uppercase and Cell B2 is in sentence case, so they are not exactly the same (MISMATCH).

• Step 3 - Remember if you click on the comparison cell, you can also see the formula used, displayed in the formula bar

In all the above methods that I have showed you so far, it deals with displaying the comparison result after comparing. What if we don't want to display an input after comparison. What other way can we do this comparison?

• COMPARING COLUMNS (HIGHLIGHTING MATCHING CELLS)
• Step 1 - In the excel sheet below, we have two columns Column 1 and Column 2. You can select create simple data in the excel worksheet below to follow up with this tutorial. Here we will be highlighting matching cells instead of displaying the result of the comparison

• Step 2 - In your worksheet, select the both columns you want to compare excluding the headings if they are any. In this case heading, I selected the Column 1 and Column 2 data excluding the headings Column 1 and Column 2.

• Step 3 - In the Home tab, go to the Styles group and click on Conditional Formatting. From the list displayed, click on the New Rule option.

• Step 4 - The New Formatting Rule dialog box will be displayed. Under the Select a Rule Type, click on the Use a formula to determine which cell to format.

• Step 5 - Next is to enter the formula under the section Edit the Rule Description, in the Format values where this formula is true input field.

• Step 6 - Now enter the formula =$A2=$B2 inside the input field as shown in the image below and click on the Format button.

• Step 7 - Next the Format Cell dialog box will be displayed. Click on the Fill tab, select the color you want to use, which after selecting you can see the sample of the color selected. Then lastly click the OK button.

• Step 8 - You will be taken back to the previous dialog box. There you will also see the color you selected for the formatting. Click the OK

• Step 9 - The matching rows will now be highlighted successfully.

• Step 10 - ALL DONE! But what if you now want to remove the highlights, what will you do?

• Step 11 - To remove the highlight from the rows, click the Home tab, and go to the Styles group. Inside the styles group, click on Conditional Formatting then go to Clear Rules option from the dropdown list displayed. Then you can select option to either clear rules from the selected cells or the entire worksheet.

• Step 12 - The highlight together with the rule will be clear from the cells successfully.

• COMPARING COLUMNS (HIGHLIGHTING DUPLICATE CELLS)
• Step 1 - In the excel sheet below, we have two columns Column 1 and Column 2. You can select create simple data in the excel worksheet below to follow up with this tutorial. Here we will be highlighting duplicate cells.
  Select the cells containing the data.

• Step 2 - In the Home tab, go to the Styles group and click on Conditional Formatting. From the list displayed, go to the Highlighted Cell Rules and another list of options will be displayed. Click on the Duplicate Values option as shown in the image below.

• Step 3 - Then Duplicate Values dialog box will be displayed, and the duplicate values in your excel worksheet will be formatted with the default formatting as shown in the image below.

• Step 4 - To change the formatting click on the dropdown arrow button beside the selection input with the tag values with. Then click on the type of formatting color you want. In our case, we have selected the Yellow Fill with Dark Yellow Text which is applied to the duplicate data as shown in the image below.

• Step 5 - You can also select the custom format which will now open the Format Cells dialog box for you to customize your duplicate cells formatting as you want.

• You can use the Number, Font, Border or Fill tabs to open their respective sections of formatting to use. In the fill tab section, you can use the pattern color, pattern style, fill effect and more colors options if you like.

• Step 6 - For now, lets just stick with using a light brown color. Click on the OK button to finalize.

• Step 7 - The duplicate cells will now be highlighted with a light brown color successfully.

• Step 8 - ALL DONE!

What if there are random duplicate data in your worksheet, how would this method above when applied look like?

• Step 1 - Now let's use this data in the image below and apply the highlight duplicate rule method for comparing data and see how it will look like.

• Step 2 - After applying the duplicate rule method, our data will be looking like this in the image below. This also gives us some options or can be useful in other ways, but it can also be done in selected situation when working with excel.

Lastly before you round up, as an added advantage to this tutorial, I want to show you how you can remove duplicate data from your worksheet without having to do it manually one by one.

• REMOVING DUPLICATES
• Step 1 - Now, open a worksheet that contains multiple duplicate data. You can create similar to the one in the image below, or you can copy this exactly one to follow up for this part of the tutorial.
  Now select the cells containing the data.

• Step 2 - Click on the Data tab, go to the Data Tools group and click on the Remove Duplicates icon as shown in the image below.

• Step 3 - This Remove Duplicates dialog box will be display. Here you have Select All, Unselect All button and My Data Has Headers checkbox options. Since you have already selected the data, just click on the OK button to remove the duplicates.

• Step 4 - Next, Excel will automatically remove all the duplicates in the worksheet. It will display a dialog box telling you how many duplicates were removed and how many unique values are still remaining. Inside this dialog box, click the OK button to continue.

• Step 5 - Back to the worksheet, duplicates are successfully removed.

• Step 6 - Removing duplicates can be so much helpful when entering names, class list, etc. It is very easy to do and gives you an edge over work speed and efficiency.