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?

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"ace21" wrote:
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 fomula?

"Max" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"ace21" wrote:
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 fomula?



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?

"ace21" wrote:
I tried 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?

Aha, so that's what you really want ..

No problem, just amend the formulas in cols B and C:
In B1: =IF(A1="","",IF(COUNTIF(A:A,A1)1,"",ROW()))
In C1: =IF(A1="","",IF(COUNTIF(A:A,A1)1,ROW(),""))
(Rest of steps earlier follows. No change to formulas in cols D and E)

The above will now return items which occur only once in col D, items
occuring more than once in col E.

"Uniques" are commonly understood to include the 1st instance/occurence. The
2nd instance onwards are called "duplicates". That's what the earlier set-up
delivers.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---