Combining Worksheets

[magatha]

I've searched the archives, and haven't found exactly this problem. I have different worksheets with lists of names that I want to merge--BUT, when they're merged I want to create a new worksheet that lists ONLY those names that are not shared by the different lists.

In other words, if worksheet A has this list:

bob
joe
cindy
mary

And B has

bob
joe
cindy
mary
jane

The new worksheet should have only the name 'jane' listed.

Thanks in advance.

[Yogi Anand]

Hi magatha:
Welcome to the board!
Let us say your NAME lists are in sheets 5 and 6, and you want to consolidate them in sheet7.
bring both the lists starting in columns A5 and B5 of sheet7 as shown in the simulated worksheet.
Give each NAME list a label -- I used Sheet5_name in cell A4, and Sheet6_name in cell B4. Put the following formula in cell A2

=NOT(A5=B5) ... range A1:A2 will be used as criterion range

Now apply Advanced Filter to the combined list with range A1:A2 as criteron --
You should notice only the name 'jane' remaining on the filtered list.

Regards!

Microsoft Excel - y020521.xls
A6		=	=Sheet5!A:A
	A	B	C	D
1
2	:alert('=NOT(A5=B5)')>FALSE
3
4	Sheet5_name	Sheet6_name
5	:alert('=Sheet5!A:A')>bob	:alert('=Sheet6!A:A')>bob
6	:alert('=Sheet5!A:A')>joe	:alert('=Sheet6!A:A')>joe
7	:alert('=Sheet5!A:A')>cindy	:alert('=Sheet6!A:A')>cindy
8	:alert('=Sheet5!A:A')>mary	:alert('=Sheet6!A:A')>mary
9		:alert('=Sheet6!A:A')>jane
10
11
12
13
14
15
16
17
Sheet7

Click on a hyperlinked cell to see the underlying formula

[magatha]

Brilliant! I was almost there, but am still wobbly....

--Magatha