Excel: Comparing two columns and output data Solved/Closed

Question

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!

venkat1926 · Accepted Answer

"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

venkat1926 · Answer

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

Siah · Answer

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 · Answer

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!

venkat1926 · Answer

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 · Answer

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 · Answer

Thanks again!  We can mark this one as solved =)

Excel: Comparing two columns and output data

7 responses

Similar discussions

Subscribe To Our Newsletter!