Excel - Compare data from two columns

December 2016



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.

Related :

This document entitled « Excel - Compare data from two columns » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.