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.
Related
- Excel - Compare data from two columns
- Comparing data in 3 columns on a Microsoft Excel spreadsheet ✓ - Forum - Excel
- Excel - Comparing cells across multiple columns - How-To - Excel
- Compare data in seperate columns to find likely matches ✓ - Forum - Excel
- Excel - Compare data in two worksheets ✓ - Forum - Excel
- Excel compare data in two worksheets ✓ - Forum - Excel
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!
