Comparing two Excel sheets

[Guest]

Is it possible to compare wo sheets of data in excel.

Example

Column A sheet 1 contains various names (members)
Column B sheet 2 also contains various names.

The names are not Necessarily in the same order in both columns and are not
all identical.

Is it possible to compare the two column and find the names in common
eventhough they are not in the same order in a 3rd sheet?

 

[Jezebel]

It's possible, but you'd need to do a lot more work on the specification
first. What's the "3rd sheet" ? What *exactly* do you mean by "in common"?

 

[Guest]

By 3rd sheet I meen a blanc sheet displaying the the result of the
comparison. In common refers to the names column A and B shares for instance
if both A and B includes names like Franc, Maria and John I want result of
the comparison to indicate that the result of the comparison are Franc, Maria
and John...

Hopefully you understand my meaning

"Jezebel" skrev:

 

[Dave Peterson]

I'd create a new worksheet (sheet3?)
Back to sheet1.
select column A and copy|paste into A1 of sheet3

Then back to sheet2 and copy the data to the bottom of that new list (avoid the
header rows).

Now you have a big old giant list (with duplicates on sheet3).

Select sheet3
select columns A
Data|filter|advanced filter
check unique records only
and choose "Copy to another location"

Use B1 for that output range.

Debra Dalgleish has some nice instructions at:
http://www.contextures.com/xladvfilter01.html#FilterUR

Delete column A (since we're done with it).

Tnen in B1, put this: On Sheet 1
In C1, put this: On Sheet 2

In b2, put this:
=isnumber(MATCH(a2,sheet1!a:a,0))

In C2, put this:
=isnumber(MATCH(a2,sheet2!a:a,0))

Now drag those formulas down

Select columns A:C
Data|filter|autofilter

You can filter to show which names are on both (show true for both columns)

Which ones are on one, but not the other (show true for one column and False for
the other).

If you have any that show False for both columns, you've made an error.