Cell Column Comparison

A

ace21

I have six columns of part numbers (A-F) that I want to compare to each
other. The part numbers that are an exact match I want displayed in column G
and the number that do not match I want displayed in column H. What is the
best way to accomplish this with an Excel fomular?
 
M

Max

One interp and formulas play (Try in a copy of your sheet) ..

Cut n paste sequentially (one below the other) the data that's in cols B to
F into col A, so that all data is in col A

Then place

In B1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)>1,"",ROW()))

In C1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)>1,ROW(),""))

In D1:
=IF(ROW()>COUNT(B:B),"",INDEX($A:$A,SMALL(B:B,ROW())))

Copy D1 to E1. Select B1:E1, copy down to last row of data in col A. Col D
returns the list of unique part#s, col E returns the list of duplicate part#s
 
A

ace21

Max said:
One interp and formulas play (Try in a copy of your sheet) ..

Cut n paste sequentially (one below the other) the data that's in cols B to
F into col A, so that all data is in col A

Then place

In B1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)>1,"",ROW()))

In C1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)>1,ROW(),""))

In D1:
=IF(ROW()>COUNT(B:B),"",INDEX($A:$A,SMALL(B:B,ROW())))

Copy D1 to E1. Select B1:E1, copy down to last row of data in col A. Col D
returns the list of unique part#s, col E returns the list of duplicate part#s
Click to expand...



I tied the formula but I get some duplicate part numbers that are in both
the unique part number list and the duplicate part number list when they
should be in either one or the other list and not both. Example: Part number
00200-08015 will be in both list when it should only be in one. Is there a
way to fix this?
 
Top