How to locate Duplicate work order numbers listing the row.

[Eloise T]

I use the following formula which looks at the array in Column A and checks for duplicate Work Order number entries. (Usually there is only one duplicate, but occasionally there can be 3 or on rare occasion, 4 duplicates.)

=IF(A3<>"",IF((COUNTIF($A$3:$A$15000,TRIM(A3))>1),"Duplicate",""),"--")

Returns "--" if no data in Column A,
"" if no duplicate found in Column A,
and "Duplicate" if a work order appears more than once.

Currently, the formula works fine; however, the array is over 10,000 rows long and it gets tedious to scroll through to find where the duplicate row(s) are when the formula flags a match. Can the formula be reengineered to also indicate, e.g. "Duplicate found in row ",nnnn?

 

[oldchippy]

Hi Whatever,

Try this
=IF(A2<>"",IF((COUNTIF($A$3:$A$15000,TRIM(A2))>1),"Duplicate in Row "&ROW(),""),"--")

 

[Eloise T]

This reports the Duplicate of the row you are looking at but not the row of where the OTHER duplicate, way up or down, in the spreadsheet exists.


Try this
=IF(A2<>"",IF((COUNTIF($A$3:$A$15000,TRIM(A2))>1),"Duplicate in Row "&ROW(),""),"--")[/quote]

 

[oldchippy]

Hi,

Not sure whether this is what you mean? (BTW it should be $A$2, not $A$3)

=IF(A2<>"",IF((COUNTIF($A$2:$A$15000,TRIM(A2))>1),"Duplicate in Row, "&A2,""),"--")


Whoops - Just seen the duplicate post here
http://chandoo.org/forum/threads/lo...listing-the-row-of-the-other-duplicate.26915/