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).

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!