How to compare two column of data and sort them out according

[kuansheng]

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.

[R..VENKATARAMAN]

try David Mcritchies's pushdown macro
in google serach for this expert's name


"kuansheng" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Ron Coderre]

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
Data>Filter>Advanced 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
Data>Filter>Advanced 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
>
>

[Ron Coderre]

Reference correction

In both instances, the Advanced Filter List Range should be: $A$1:$B$10
instead of A$1:$A$10

Does that help?

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

XL2002, WinXP-Pro


"Ron Coderre" wrote:

> 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
> Data>Filter>Advanced 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
> Data>Filter>Advanced 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
> >
> >