Comparing Data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BK - 15 Jan 2008 23:10 GMT

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?

Reply to this Message

Pete_UK - 15 Jan 2008 23:59 GMT

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

> Using Excel 2003
>
[quoted text clipped - 21 lines]
> Is there some other way of accomplishing this comparison? Anything that
> would return "Johnny" as a unique entry in Column B?

Reply to this Message

BK - 16 Jan 2008 02:24 GMT

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

On Jan 15, 11:10 pm, "BK" <nos...@nospam.com> wrote:

> Using Excel 2003
>
[quoted text clipped - 22 lines]
> Is there some other way of accomplishing this comparison? Anything that
> would return "Johnny" as a unique entry in Column B?

Reply to this Message

Pete_UK - 16 Jan 2008 13:45 GMT

Glad to be of help - thanks for feeding back.

Pete

> Wow! That worked great! Thanks so much!
>
[quoted text clipped - 43 lines]
>
> - Show quoted text -

Reply to this Message

Evgeny Grischenko - 24 Jan 2008 13:46 GMT

Hi!
Try this simple add-in for Excel
http://www.office-excel.com/excel-addins/compare-spreadsheets.html
Hope it helps!
Eugene

> Using Excel 2003
>
[quoted text clipped - 21 lines]
> Is there some other way of accomplishing this comparison? Anything that
> would return "Johnny" as a unique entry in Column B?

Reply to this Message