How to find duplicate names in different rows
Posted by Jonathan on June 20, 2001 2:21 PM
I have a rather perplexing one here. I have a spreadsheet that is rather large (will get up to 12000 rows). I need to match the criteria in 3 columns to see if I have duplicates in rows. Data sort works, but I must scroll down the entire spreadsheet to find a dupe by row after it's been sorted. Example:
columna a (lastname), column b (firstname), column c (customerid), column d-w (misc. info).
Thanx all,
Jonathan
Posted by Ben O. on June 20, 2001 2:50 PM
I would recommending using a conditional sum/count formula. The formula will count the rows if they match your criteria, which will be the first name, last name, and ID. If the formula's result is 1, there are no duplicates. If it's 2 there's 1 duplicate, 3 there are two duplicates, etc.
If you have the Conditional Sum Wizard installed, I would use that. Otherwise use this formula, remembering to press Ctrl + Shift + Enter when you enter it (since it's an array formula) and to change the ranges to the limits of your data:
{=SUM(IF($B$2:$B$12000=B2,IF($A$2:$A$12000=A2,IF($C$2:$C$12000=C2,1,0),0),0))}
Just enter the formula in X2 and autofill it all the way down. I hope it works,
-Ben
P.S. This is a lot easier with Access
Posted by Aladin Akyurek on June 20, 2001 3:06 PM
Or...
use conditional formattting to highlight the duplicate.
Lets say you have the following data in A1:C5.
{"a","b",1;"a","b",1;"c","x",2;"d","z",5;"d","z",5}
Type in some unused cell the following formula:
=AND(ISNUMBER(MATCH(A2,$A$1:A1,0)),ISNUMBER(MATCH(B2,$B$1:B1,0)),ISNUMBER(MATCH(C2,$C$1:C1,0)))
Go to the formula bar and copy this formula.
Activate C2 (we start in 2n row), activate the option Format|Conditional Formatting, choose "Formula is", now paste the copied formula. Activate the Patterns tab and select a color and click OK.
While in C2, click the icon for Format Painter, select the cells from C3 onwards and let it go.
A colored C cell indicates a duplicate row.
Aladin
