0
Thanks

A few words of thanks would be greatly appreciated.

Excel - Compare data from two columns

Issue

I'm using Excel 2007 and am trying to figure out a way to analyze and sort some data.

I have two sets of data in 6 and 5 columns in the same worksheet, each approximately 2000 rows in length. Columns A, B, C, D, E, and F include the following data respectively (Name, Address, City, State, Zip, Phone Number). Columns G, H, I, J and K include the following data respectively (Name, Address, City, State, Zip).

I'm wanting to compare the two Address columns (Column B and Column H). If an address in Column B is not found in Column H, I would like the entire row associated with that particular cell to be output into either a separate sheet or in columns M, N, O, P, Q and R. For example, if there is an address in cell B4 that is not found anywhere in column H, I would like cells A4, B4, C4, D4, E4 and F4 to be displayed in a separate sheet or columns M, N, O, P, Q and R, whichever might be possible or easier.

Solution

E.g if data is listed as below:

Name     Address         City    State   Zip     Phone Number   Name     Address         City    State   Zip 
x       asd     x       x       x       x       x       asd     x       x       x 
x       fgh     x       x       x       x       x       ghk     x       x       x 
x       wer     x       x       x       x       x       yui     x       x       x 
  • In M2 type this formula and drag it over the concerned cells.
  • =IF($B2<>$H2,A2,"") 

Thanks to venkat1926 for this tip.

0
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
This document, titled « Excel - Compare data from two columns », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).