Show corresponding duplicate row number (Excel 2003)

Topic

New Reply

Hi all,

Using this formula will shows you the same row number of the duplicate item.

=IF(COUNTIF($A$1:$A$10,A1)>1,ROW(),””)

Result : if cell A1 and A7, this will show the row as 1 and 7 respectively.

How can I tweak it to show the corresponding duplicate row number?

eg. cell A1 will show the result of A7 and cell A7 will show the result A1

Is this possible?

regards, francis

Reply | Quote

Replies

The request you have poses some complications if there are more than two duplicates. What must happen if there are three or more duplicates. This is then not just a matter of inverting the number?

Reply | Quote

Francis

By far the easiest method in this situation is to use Conditional Formatting using the format in the image below,as Rudi suggests what is to happen if there is more than one duplicate. At Least this highlights them

Reply | Quote

Hi Jezza,

Thanks, agreed that conditional formatting does serves it purpose in finding duplicates. .

Hi Rudi, thank for the headup!
if that happen as suggested, is it possible to have the third duplicate show the row number of the first two,

regards, francis

Reply | Quote

How about:
=IF(COUNTIF($A$1:$A1,A1)>1,”A”&MATCH(A1,$A$1:A1,0),””)

Steve

Reply | Quote

Hi Steve,

this work as expected.Would you mind explain what the “A” in front of & is for. I understand the first part and the last part of the formula, but
unsure about the “A”.

cheers, francis

Reply | Quote

You wanted the formula to return something like A3 or A7.
MATCH(A1,$A$1:A1,0) is the row number such as 3 or 7. “A” puts the A in front of the row number.

Reply | Quote