0
Thanks

A few words of thanks would be greatly appreciated.

How To Compare Two Columns in Excel

This tutorial will walk you through how to use the COUNTIF formula on Microsoft Excel to see if entries of a specified column are also present in another column.

How To Compare Numbers in Two Columns on Excel


Issue

I need to compare numbers in two columns of Excel to find duplicates in data. Both columns have essentially the same numbers, but Column B has multiple instances of the same number. Column A is unique. How can I ascertain if every number in Column A is present in Column B at least once? How can I find the number that is missing from Column B?


A B    
B.121 S.222
S.154 B.121
B.164 S.154
B.187 B.154

Solution

Find duplicates in your data by using the COUNTIF formula. This can be done by first adding an additional column to your table. Assuming your data in Columns A and B begin at line two, insert the following formula into Column C2:

=COUNTIF(B:B,A2) 

The resulting number shown in Column C2 will tell you how many times the value in A2 appears in column B.

Compare all data in the table by highlighting C2 and dragging the formula to the last row of data entry in Column C.

More tips about comparing and pasting in Excel can be found here:

http://ccm.net/faq/4698-excel-comparing-cell-a1-to-entire-a-column-in-sheet-2

http://ccm.net/faq/5475-excel-comparing-and-pasting

Note

Thanks to rizvisa1 for this tip on the forum.
0
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
Jean-François Pillou

CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Learn more about the CCM team

Published by . Latest update on by Christina Langer.

This document, titled "How To Compare Two Columns in Excel," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (https://ccm.net/).

0 Comments