compare two columns in excel and delete matches

COMPARE TWO COLUMNS IN EXCEL AND DELETE MATCHES HOW TO

It could exist once, twice or more times. If this count is more than 0, it means a duplicate of the B2 value exists in column A. The COUNTIF function counts the number of times the value in B2 occurs in the cell range A2:A9. In other words, we wanted to highlight only those items of column B that have 1 or more occurrences in cells A2 to A9. In this problem, we wanted to know which ingredients of column B also had an occurrence in column A. You should now see all cells (ingredients) of column B (cookies) that are also present in column A (cake) highlighted in your selected color. Finally click the Done button, to let Conditional formatting do its work.Select the color you want to use, in order to highlight matching cells/rows.Under “ Formatting style”, click on the Fill Color button ( ).Type your custom formula there: =COUNTIF($A$2:$A$9,B2)>0 You will see an input box below the dropdown list.From the dropdown menu that appears, select “ Custom formula is”.Next, in the Format rules section, under “ Format cells if”, click on the dropdown arrow.In our example, we want to see the highlights of column B only. In the input box under “Apply to range”, type in the range of cells you want to apply the formatting on.

This will open the ‘ Conditional format rules’ sidebar on the right of the window.Select the ‘ Conditional Formatting’ option.Click the Format menu from the menu bar.Here’s how you can use Conditional formatting to highlight duplicate data: That would make more sense for our sample data too wouldn’t it? You might want to know which ingredients of a cake are also used in baking cookies. So far, the techniques we discussed mainly found out if cells in the same row were matching.īut what if you want to compare two columns and find out which values of column A are repeating in column B, irrespective of which row it’s in? Using Google Sheets Conditional Formatting to Compare Two Columns and Find Matching Data For example, you could use a formula like: = IF ( C2 $B2 You could use any operator or formula you’d like to show results for as a logical expression. Just like in the above example, you could use an IF statement on numerical data. You should now see the results of each comparison as “ Matching” or “ Not Matching” in column C.Ĭompare Two Columns and Show Meaningful Text From Numerical Values In the second row of column C (in cell C2), insert the formula: =IF(A2=B2,”Matching”,”Not Matching”).If the condition “A2=B2” is TRUE, it returns the text “ Matching”. The above formula uses an IF function to compare values in A2 and B2. If you want to get a more descriptive result, you can use a simple IF formula to return the text “ Match” when the values are the same and “ Mismatch” when the values are different.įor this, assuming you were working in cell C2 of our example, you would use the following formula: =IF(A2=B2,'Matching','Not Matching') For example, you could use the word “ Matching” for row items that match and “ Not matching” for row items that don’t match. So it makes sense to give a more descriptive result in column C. The above method works great if you’re the only one who will be working with the data.īut, a third-person going through your worksheet might not understand what exactly you’re implying by the words “TRUE” and “FALSE”. You should now see the results of each comparison in column C.Ĭompare Two Columns and Show Meaningful Text Double click the fill handle to copy the same formula to the rest of the cells in column C.In the second row of column C (in cell C2), insert the formula: =A2=B2.

The above formula compares data in row 2 of both columns to see if they match. The formula you would use for a comparison like this is: =A2=B2 In our example, we are simply going to create a new column for the results, and display a TRUE if the pair of items in the same row match and a FALSE if they don’t. Google sheets just need to see which rows have the same values and which ones have different values and display the result in a third, blank column. All this calls for is just a row-by-row comparison. The simplest way to compare two columns is to find exact row matches.

COMPARE TWO COLUMNS IN EXCEL AND DELETE MATCHES HOW TO

How to Compare Two Columns for Exact Row Matches To do this you will have to use the IMPORTRANGE function to be able to refer to the columns in another sheet. But you can as well use the same techniques to compare two columns that are in different Google Sheets documents. Also, note that in this case, we are comparing columns that are in the same worksheet.

Compare two columns in excel and delete matches