Compare lists on different worksheets to identify overlap

[CJWP]

Version: 2008
Operating System: Mac OS X 10.4 (Tiger)
Processor: Intel

I would like to compare two data sheets based on their row headers.

Each row is labeled by a unique row header (Name) in the first column. Each row header occurs only once on a given worksheet, but different subsets of this list of Names may be present on different work sheets.

When comparing two worksheets, I would like to know which of the Names are present in both of the worksheets that are being compared. I.e. which Name occurs in both worksheets? And can I output the entire row data (i.e. the data associated with that name from one of the two lists) to a 3rd worksheet?

I have tried sorting and comparing, but the problem is that while a given Name may be present in both sheets, it does not reside in the same place (because the lists may even have different lengths) and so all the IF functions I have tried keep failing.

In this example, only four names are shared between the two Sheets/Lists (1389306_at, 1378480_at, 1392785_at, 1387147_at). Besides finding these four, I would like to output the associated data from each row (imaging there are columns of information to the right of each name/row header) to a new table or sheet.

Sheet1
Column A Column B Column C etc
Name Other info Other info
1389306_at
1368869_at
1379614_at
1382535_at
1378480_at
1392785_at
1373463_at
1367562_at
1387147_at

Sheet2
Column A Column B Column C etc
Name Other info Other info
1389306_at
1365439_at
1378480_at
1392785_at
1379234_at
1359997_at
1387147_at

 

[JE McGimpsey]

Version: 2008
Operating System: Mac OS X 10.4 (Tiger)
Processor: Intel

Each row is labeled by a unique row header (Name) in the first column. Each
row header occurs only once on a given worksheet, but different subsets of
this list of Names may be present on different work sheets.

When comparing two worksheets, I would like to know which of the Names are
present in both of the worksheets that are being compared. I.e. which Name
occurs in both worksheets? And can I output the entire row data (i.e. the
data associated with that name from one of the two lists) to a 3rd worksheet?

One way:

If you just want to display the dups, see

http://cpearson.com/excel/duplicates.aspx

To move them, try this. In worksheet Sheet1, use a helper column, e.g.:

J1: Duplicates
J2: =COUNTIF(Sheet2!A:A,A2)

copy down as far as necessary. (I'm assuming you're using headers in row
one)

Enter a criteria range in, say, L1:

L1: Duplicates
L2: =J2>0

Now select your third sheet. Choose Data/Filter/Advanced Filter. Click
the Copy to another location. Enter

List Range: Sheet1!A:J
Criteria Range: Sheet1!L1:L2
Copy to: A1

Click OK