• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 539
  • Last Modified:

compare two excel columns

How I can compare two excel columns and get the differences between them in a third column??

Column A included 970 lines
Column B included 1289 lines
Some of data are same between them.


Thanks
0
ACS2012
Asked:
ACS2012
  • 3
  • 2
1 Solution
 
SteveCommented:
in column C cell C2 enter =COUNTIF(A:A,B2) and copy down...
this will tell you how many times the value in column B is in columnA
0
 
Steve KnightIT ConsultancyCommented:
You could do a few more columns too.  See attached.
test-sheet.xlsx
0
 
ACS2012Author Commented:
this is not working, I try both solutions

all what I need is to compare column with column ,,,,

I attached the list of IP's which I need to compare,,, column A included less than column B,,,

I need to know what the IP's in column B which they are not in Column A
IP---Copy.xlsx
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
SteveCommented:
Here is the file with the formula as stated in my first post.

Zero = not in column A
IP---Copy.xlsx
0
 
Steve KnightIT ConsultancyCommented:
I copied my formulae in too.

Column C = TRUE when column B is found in column A
Column D = TRUE when column A is found in column B
Column E = TRUE when it is only found in column A
Column F = TRUE when it is only found in column B

Use the auto filter on column E or F to get a list of those that are in one column or the other.

ALL entries in column A match with column B though
So the list where column F = True are the ones that are "extra" in column B.

But for example 132.22.21.3 appears in column A and B multiple times.

From autofilter, there are some 478 entries in column B that are not in column A.
IP---Copy.xlsx
0
 
Steve KnightIT ConsultancyCommented:
Thanks for the grade.  Did that get you what you needed?

Thanks

Steve
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now