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

  • Thread starter Thread starter kuansheng
  • Start date Start date
K

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.
 
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
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
Back
Top