Seperating duplicate cells

[Hayley1982]

I have two columns (column a & column b) column A has a list of 29,000
chassis numbers, column B has a list of 11,000 chassis numbers, i need a way
to create a new column that shows a list of all the chassis numbers that do
not match between columns a & b

 

[Max]

One way ..

Assuming the data is in cols A (29K) and B (11K),
from row1 down

Put in D1:
=IF(ISERROR(SMALL(E:E,ROWS($A$1:A1))),"",INDEX(A:A,MATCH(SMALL(E:E,ROWS($A$1
:A1)),E:E,0)))

Put in E1:
=IF(ISNUMBER(MATCH(A1,B:B,0)),"",ROW())

Select D1:E1, copy down to E29000

Col D will return all chassis #s in col A which do not match with those in
col B, neatly bunched at the top

Put in F1:
=IF(ISERROR(SMALL(G:G,ROWS($A$1:A1))),"",INDEX(B:B,MATCH(SMALL(G:G,ROWS($A$1
:A1)),G:G,0)))

Put in G1:
=IF(ISNUMBER(MATCH(B1,A:A,0)),"",ROW())

Select F1:G1, copy down to G11000

Col F will return all chassis #s in col B which do not match with those in
col A, neatly bunched at the top

 

[greg7468]

Hi Hayley,

assuming your data is in A1;A29000 and B1:B11000

In C1 put this formula


=IF(B1=VLOOKUP(B1,A1:A29000,1),"match",B1)

This will give either a match or the chassis number if there is no
match

you can then sort the data in column C numerically ascending to give
all the chassis numbers to the top

Or you could autofilter > custom column C and show values not equal to
match

Don't forget column A will have to be in numerical order for VLOOKUP to
work.

HTH.