Comparing Data

  • Thread starter Thread starter BK
  • Start date Start date
B

BK

Using Excel 2003

I have a list of names in Column A:

Billy
Susie
Sally
Fred

I have another list of names in Column B:

Billy
Susie
Johnny
Sally
Fred

I want to compare the two columns of data to find names in Column B that
are not in Column A. I cannot use the "match" function because one extra
name in Column B (Johnny) throws off the side-by-side match of the two
columns. Sally and Fred are both still in each column even though the extra
name "Johnny" causes them to be offset from their match in Column A.

Is there some other way of accomplishing this comparison? Anything that
would return "Johnny" as a unique entry in Column B?
 
Assuming your data starts in row 1, then you can put this formula in
C1:

=COUNTIF(A$1:A$5,B1)

and copy this down. This will count the number of times that the name
in B occurs in A (adjust the range to suit your data), so any values
of 0 indicate that the name in B does not occur in column A. Hence you
can apply autofilter on column C and select 0 to give you the names
which are unique to column B.

Hope this helps.

Pete
 
Wow! That worked great! Thanks so much!



Assuming your data starts in row 1, then you can put this formula in
C1:

=COUNTIF(A$1:A$5,B1)

and copy this down. This will count the number of times that the name
in B occurs in A (adjust the range to suit your data), so any values
of 0 indicate that the name in B does not occur in column A. Hence you
can apply autofilter on column C and select 0 to give you the names
which are unique to column B.

Hope this helps.

Pete
 
Back
Top