Try this:

Note the $ signs in the references below. They are critical.

First, stack the lists so the 2nd list is below the other with device
references next to each part.

Example (in cells A1:B10):
Part Device
a Widget
b Widget
c Widget
d Widget
b Gadget
c Gadget
w Gadget
x Gadget
y Gadget

Next, a little setup
D1: Test
D2: =COUNTIF($A$1:$A$10,A2)=1

F1: Unique
F2: Part
G2: Device

I1: Shared
I2: Part

Extrracting the data is next:
Select A1:A10
DataFilterAdvanced Filter
Check: Copy to another location
List range: (already selected $A$1:$A$10)
Criteria range: $D$1:$D$2
Copy to: $F$2:$G$2
Click the [OK] button
(That extracts the unique part numbers)

In this example:
Unique
Part Device
a Widget
d Widget
w Gadget
x Gadget
y Gadget


Next, change the criteria formula:
D2: =COUNTIF($A$1:$A$10,A2)1

Again, select A1:A10
DataFilterAdvanced Filter
Check: Copy to another location
Check: Unique records only
List range: (already selected $A$1:$A$10)
Criteria range: $D$1:$D$2
Copy to: $I$2
Click the [OK] button
(That extracts the part numbers used by both devices)

In this example:
Shared
Part
b
c

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"kuansheng" wrote:


Hi,
I have this problem, What i have on hand is two column of data
containing part number of two different devices. What i am trying to do
is to compare this two column and find out all the part number that is
common to both device and also which part number is unique. And
organise and display this in excel. Anyone know any way to accomplish
it please help.


--
kuansheng
------------------------------------------------------------------------
kuansheng's Profile: http://www.excelforum.com/member.php...o&userid=30658
View this thread: http://www.excelforum.com/showthread...hreadid=503578