If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Cell Column Comparison
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? |
#2
|
|||
|
|||
Cell Column Comparison
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? |
#3
|
|||
|
|||
Cell Column Comparison
"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? |
#4
|
|||
|
|||
Cell Column Comparison
"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 --- |
Thread Tools | |
Display Modes | |
|
|