Thread: Show corresponding duplicate row number (Excel 2003)

1. Show corresponding duplicate row number (Excel 2003)

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

2. Re: Show corresponding duplicate row number (Excel 2003)

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?

3. Re: Show corresponding duplicate row number (Exce

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 <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

4. Re: Show corresponding duplicate row number (Exce

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, <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

regards, francis

5. Re: Show corresponding duplicate row number (Excel 2003)

=IF(COUNTIF(\$A\$1:\$A1,A1)>1,"A"&MATCH(A1,\$A\$1:A1,0) ,"")

Steve

6. Re: Show corresponding duplicate row number (Excel 2003)

Hi Steve,

<img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> 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". <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

cheers, francis

7. Re: Show corresponding duplicate row number (Excel 2003)

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.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•