| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How to compare two column of data and sort them out according
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 |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
How to compare two column of data and sort them out according
try David Mcritchies's pushdown macro
in google serach for this expert's name "kuansheng" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
How to compare two column of data and sort them out according
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 |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
How to compare two column of data and sort them out according
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 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
| Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
| Sort pages? | Excel Discussion (Misc queries) | |||
| sort column data with hidden columns - excel 2003 | Excel Discussion (Misc queries) | |||
| How do I sort a column of data and have each data row sort accordi | Excel Discussion (Misc queries) | |||
Linear Mode
