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!
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!
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!
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!