How to compare the lists in two columns & separate odd one ?

[Nikhil]

i wish to compare list of components in WO columns and then sort out the item
which is not present in First List ?

[Max]

One play to try ..

Assume data in cols O and W, from row1 down
The "First List" is presumed to refer to col W

Using 2 empty cols to the right, say cols Y and Z

Put in Y1:
=IF(ISERROR(SMALL(Z:Z,ROWS($A$1:A1))),"",INDEX(O:O,MATCH(SMALL(Z:Z,ROWS($A$1
:A1)),Z:Z,0)))

Put in Z1:
=IF(ISNUMBER(MATCH(O1,W:W,0)),"",ROW())

Select Y1:Z1, copy down until the last row of data in col O

Col Y will return the items in col O not present in col W (the :First
List"). Items will be neatly bunched at the top in col Y.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Nikhil" <Nikhil@discussions.microsoft.com> wrote in message
news:CB3E5538-EE35-4A90-942A-27940ABF30C6@microsoft.com...
> i wish to compare list of components in WO columns and then sort out the
item
> which is not present in First List ?

[bj]

make a helper column next to the second list and enter
=if(iserror(vlookup(B2,[Initial list],1,false),1,"")
copy down to the end of your second list
Use auto format on your helper column and select 1.
This will be a list of items in the second list which are not in the first
list.


"Nikhil" wrote:

> i wish to compare list of components in WO columns and then sort out the item
> which is not present in First List ?