Locating duplicate work order numbers & listing the row of the OTHER duplicate.

[Eloise T]

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

=IF(A3<>"",IF(SUMPRODUCT((TRIM($A$3:$A$15000)=TRIM(A3))+0)>1,"Dup. WO# in Row ",""),"--")

Returns "--" if no data in Column A,
"" if no duplicate found in Column A,
and "Dup. WO# in Row " 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. "Dup. WO# in Row " nnnn?

Simply concatenating the row in the formula: &ROW()

=IF(A3<>"",IF(SUMPRODUCT((TRIM($A$3:$A$15000)=TRIM(A3))+0)>1,"Dup. WO# in Row "&ROW(),""),"--")

...only returns the row you are currently looking at, not the OTHER duplicate(s) row(s). Please see attachment sample.

Can the other Duplicate row be returned?

 

[Marc L]

Just use a filter !

Otherwise you could check the next duplicate row, try this in C2 or J2 cell :
=MATCH($A2,$A3:$A$18,0)+ROW() … Copy down …
​

 

[Eloise T]

That seems to work. So what is the best way to merge the two formulas together? The first one figures out that there's a duplicate; the second one returns the row of the other matching duplicate.

=IF(A2<>"",IF(SUMPRODUCT((TRIM(A$2:A$18)=TRIM(A2))+0)>1,"Dup. WO# in Row "&ROW(),""),"--")

=MATCH($A2,$A3:$A$18,0)+ROW()

=IF(A2<>"",IF(SUMPRODUCT((TRIM(A$2:A$18)=TRIM(A2))+0)>1,"Dup. WO# in Row "&MATCH($A2,$A2:$18,0)+ROW(),""),"--")

But the results don't quite work in every case.

 

[Asheesh]

Hi,

If I understand it correct.. try using below in E2 and drag it down..

IF(COUNTIF($A$2:$A$18,$A$2:$A$18)>1,"Duplicate found in Row "&ROW()*MATCH(A2,$A$2:A2,0)^0,"-")

 

[Eloise T]

That reported a duplicate in every row...which of course there is not.

Is there an Excel function similar to MATCH that can look at the whole array and report all the duplicate matches?

Please Note: Updated spreadsheet attached.

 

[Eloise T]

Hi,

If I understand it correct.. try using below in E2 and drag it down..

IF(COUNTIF($A$2:$A$18,$A$2:$A$18)>1,"Duplicate found in Row "&ROW()*MATCH(A2,$A$2:A2,0)^0,"-")

Did you mean to raise the MATCH formula to the power of 0?

 

[Eloise T]

Hi,

If I understand it correct.. try using below in E2 and drag it down..

IF(COUNTIF($A$2:$A$18,$A$2:$A$18)>1,"Duplicate found in Row "&ROW()*MATCH(A2,$A$2:A2,0)^0,"-")

UPDATE: Please see the newest uploaded file using the merged formulas of two columns to "come close" to the desired results. At this point, the formula finds the first duplicate in the array and correctly points to the second, but the formula does not circle back around to identify where the duplicate "lives" at the row of the second duplicate.

The need here concerns being at the 10,000th row or worse, being notified there's a duplicate somewhere above, and therefore having to scroll upward through thousands of rows to identify where the first duplicate Work Order number is located.

Was what too confusing? Any ideas of how to resolve this?

 

[Eloise T]

Using Excel's "Evaluate" I was able to determine the following:
IF(TRUE,IF(SUMPRODUCT({0;0;0;0;1;0;0;0;0;0;1;0;0;0;0;0})>1,"Dup. WO # in Row "&MATCH($A13,$A14:$A$18,0)+ROW(),""),"--")
The "1s" show duplicates at rows 7 and 13. How can that information be passed on to the 2nd half of the formula?....+ROW() ? ...so that either duplicate will "point" to the other duplicate row number?

 

[Asheesh]

hey,

Check the below one...I misunderstood the requirement initially..moreover, the first construction was not an array construction

IF(((COUNTIF($A$3:$A$15,A3:$A$15)>1)*ROWS($A$3:$A3))>0,"Duplicate Row no "&MAX(IF($A$3:$A$15=A3,ROW($A$3:$A$15))),"-")

To entered as CSE and then drag down

Check column F of the attached..

 

[Eloise T]

The data in rows 3 and 7 look down and correctly identify the duplicates in rows 15 and 13; however, 15 and 13 do not correctly identify their duplicates in 3 and 7.

Also, I added a triplicate in row 10 to match rows 7 and 13 just to see what happens.

Is Excel capable of looking up or in a circular fashion to correctly detect the "upper" row numbers in the lower rows?

 

[Luke M]

Conversation continued here:
http://chandoo.org/forum/threads/id...s-accurately-reporting-the-row-numbers.26959/