A few words of thanks would be greatly appreciated.

Excel - Compare data from two columns

Latest update on July 31, 2012 at 06:57 AM by aakai1056 .

Issue
Solution

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.

Excel - Compare data from two columns
Comparing data in 3 columns on a Microsoft Excel spreadsheet ✓ - Forum - Excel
Compare data in seperate columns to find likely matches ✓ - Forum - Excel
Excel - Compare data in two worksheets ✓ - Forum - Excel
Excel match data in two columns and import third ✓ - Forum - Excel
Excel compare cells in two columns ✓ - 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).

How To Insert Multiple Blank Rows in Excel

Transfer Data From One Excel Sheet to Another

Issue

Solution

Subscribe To Our Newsletter!