Excel: Comparing two columns and output data [Solved/Closed]

Siah 5 Posts Wednesday July 25, 2012Registration date July 28, 2012 Last seen - Jul 25, 2012 at 09:48 PM - Latest reply: Siah 5 Posts Wednesday July 25, 2012Registration date July 28, 2012 Last seen
- Jul 28, 2012 at 09:51 PM
Hello,
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.

Does anybody have any ideas of how this might be accomplished? Thanks!
See more 

7 replies

Best answer
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Jul 27, 2012 at 09:40 PM
3
Thank you
"li8ike" function does not work in excel probably.

if the last word is the only problem you can compare some sort of "parts" find but that will become complicated and some "left" function also may help

those things will compllicate

Thank you, venkat1926 3

Something to say? Add comment

CCM has helped 1704 users this month

venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Jul 26, 2012 at 06:49 AM
1
Thank you
the data from A1 to K4 is like this


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
=IF($B2<>$H2,A2,"")

copy M2 across to R2
and highight M2 to R2
take the cursor to bottom of R2 cursor turns to + sign
click that plus sign

alternatively copy M2 to R2 and down next two rows.

the result m1 to R4 will be(M1 row and M2 row wil be blanks
and these will be in M3 and M4 row



x fgh x x x x
x wer x x x x
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Jul 26, 2012 at 11:28 PM
1
Thank you
INSTEAD of the old formula try this formula

=IF(B2<>$H$2:$H$4,A2,"")

NOW YOU MUST INVOKE THIS FORMULA IN M2 WITH
CONTROL SHIFT ENTER
copy M2 to to other cells(in the paste range do not include M2)
Siah 5 Posts Wednesday July 25, 2012Registration date July 28, 2012 Last seen - Jul 26, 2012 at 09:22 AM
0
Thank you
Thanks a ton, this is exactly what I've been trying to figure out! I've found one catch though and am curious if you have any ideas. My two address lists comes from two different sources, so an address in column B might be "1234 Main St" while an address in column H might be "1234 Main Street" in which the data is copied to rows M to R since it views this as a different cell, even though it's actually the same. Is there any way to only use the first 6 characters in a cell for the comparison, such that only "1234 M" would be used from column B in comparison with "1234 M" of column H, so this would be viewed as the same data and not be repeated in cells M to R, or any other possibility that might work? Thanks a lot already though!
Siah 5 Posts Wednesday July 25, 2012Registration date July 28, 2012 Last seen - Jul 26, 2012 at 09:52 AM
0
Thank you
I've actually noticed an issue with what I'm needing to do. This seems to be a very good push in the right direction, but I think unless I set things up wrong that this formula is only comparing things in this manner: B2 with H2, B3 with H3, etc.

I have two completely different lists from different sources, so things are in different orders too. I'm basically needing to compare, for example, B2 with EVERY cell in column H in looking for a match. Would I need to set up some sort of macro to do this? Also, the search can't be case sensitive, so that "1234 Main St" and "1234 MAIN ST" are recognized as the same. Thanks again!
Siah 5 Posts Wednesday July 25, 2012Registration date July 28, 2012 Last seen - Jul 27, 2012 at 04:18 AM
0
Thank you
This seems to do the trick, thanks! The only issue now is that of one cell being slightly different than the other, such as "1234 Main St" in column B versus "1234 Main Street" in column H seeming to Excel to be different, but that doesn't happen all that often. Even with this, the spreadsheet is very effective for what I need. Thanks again!
Siah 5 Posts Wednesday July 25, 2012Registration date July 28, 2012 Last seen - Jul 28, 2012 at 09:51 PM
0
Thank you
Thanks again! We can mark this one as solved =)