We help IT Professionals succeed at work.

Excel compare columns

bt707
bt707 asked
on
Medium Priority
295 Views
Last Modified: 2012-05-12
Not very good with excel other than a few simple things but have a sheet I need to do a compare with and wanted to use excel on this.

In the attached file I have 3 columns.
A: Country-one   B:Country-two  C:Region-two

Column B and C goes together where B is the Country and C is the Region that goes with B

I need to find out what Countries that is in Column B are not in the list of Countries in Column A and vise versa.

How can I make two new columns, D and E where i can (compare A and B)
Column D - contains what is in A but not B
Column E - contains what is in B but not in A

Thanks for any help

out-conf.xlsx
Comment
Watch Question

Author

Commented:
Sorry, please see updated file.

The first one is a bit messed up as I have a list of Countries in A that is smaller than B and is a big part of what I need to see what is in A and not B and in B and not A.

Thanks
out-conf--2.xlsx
CERTIFIED EXPERT

Commented:
not sure i follow..

you want to search column B with field A2 and show in D if it's in the column or do you want all the values from A that are not in B..

Author

Commented:
I have a list of countries in column A and in column B, I need to see which countries that is in A is not in B and another column where I find what countries that is in column B is not in A.

so A and B both contain countries, I need compare A and B and see what is not in each column when compared to each other.

Author

Commented:
Yes, I want to see all values from A that is not in B and all values that is in B that is not in A
CERTIFIED EXPERT

Commented:
You can't do this with normal excel functions.

you would have to right a vba script that loops through the column and does a vlookup to see if the value is present in the other column..

you can do it for 1 cell but not for the whole column.

is this a one time thing?
you can import the lists into access and query it.. that would work.

Author

Commented:
That's ok if I cannot do that, I just thought it would be easy in excel, I can do it on the server with Perl, I just that I could do this with excel.

Thanks
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010
Commented:
Hello,

see attached file. I have removed the trailing blanks from column B, because they made the comparison of A and B difficult. ( select column B > Data > Text to columns > Delimited > tick Space > Finish)

Then this formula in column D

=IF(LEN(A2)>0,IF(COUNTIF(B:B,A2),"",A2),"")

and this formula in column E

=IF(COUNTIF(A:A,B2),"",B2)

You can use Autofilter to sort the results by the new columns or just display the ones with values.

cheers, teylyn



out-conf--2.xlsx
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
p_nuts,

ehm, you CAN do this with formulas in Excel. You can even build a contiguous list of values with formulas. Don't be so quick to say "It cannot be done." just because you don't see a way to do it.

cheers, teylyn
Glenn RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014
Commented:
The issue with this comparison is that the data in column B is not trimmed (has extra spaces after characters).  Interestingly, casing is not a problem.

Without modifying the data, one can insert the following two formulas in cells D2 and E2 to get the values you need

Column D - "Country-One Codes NOT in Country-Two"
=IF(ISERROR(VLOOKUP(A2,TRIM(B:B),1,FALSE)),A2,"")
This is an array formula, meaning that you have to press CTRL+SHIFT+ENTER to enter it.  Curly brackets { } will appear around it if entered correctly.  You can copy this formula down as far as the codes in column A (row 97).

Column E - "Country-Two codes NOT in Country-One"
=IF(ISERROR(VLOOKUP(TRIM(B2),A:A,1,FALSE)),TRIM(B2),"")
This is a regular formula.  You can copy this down as far as the values in column B (row 247).

I've attached a file showing all this.
-Glenn
 out-conf--2-mod.xlsx
Glenn RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
and p_nuts,

teylyn is right...don't be so quick to discount solutions...especially here in EE.  I've seen some amazingly brilliant solutions using "regular" Excel functions.  And there's usually more than one way to solve these problems, as I hope was demonstrated here.

-Glenn

Author

Commented:
Thanks to all for the info, it was just a one time compare, I think the trailing spaces was from a copy paste I did from the output on a terminal screen where I ran some Perl to get the list I used.

Thanks All !!
CERTIFIED EXPERT

Commented:
I hear what you were saying .. but that's actually my first question.. do you want the list of per cell value.

i think it's more confusion that anything else..

i was under the impression that bt707 wanted a list without empty cells of the values.

and if that's the case please explain how it can be done. sure it's a one off and we can use filters to take them out.. i was just under the impression that the simple answer wouldn't do.
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
p_nuts,

attached is a formulaic approach that produces contiguous lists (without blank rows) for the two comparisons.

Others more versed than me can probably also do it without helper columns.


out-conf--2.xlsx

Explore More ContentExplore courses, solutions, and other research materials related to this topic.